Data format for date variable from Excel to GAUSS

Dear all,

I have an .xlsx file with monthly data and my date variable specified like this: 2003M01 for January 2003, 2003M02 for February 2003, etc. I would like to import this time variable in GAUSS. When I declare this date variable as a character it gives unsatisfactory results. Therefore, could you please help me to correctly import this time variable in GAUSS? Thank you in advance for your help.

 

3 Answers



1



I don't think that Excel can treat 2003M01 as a date. I think it will instead be a string. So, if you want the dates just for printing, you could read them in as a string with ;xlsReadSA.

myDates = xlsReadSA("myfile.xls", "A2:A3", 1, "");
print myDates;

Assuming that the 'A2' and 'A3' elements were 2003M01 and 2003M02, then the print statement above would return:

2003M01
2003M02


0



Thank you very much for your quick reply.

I would like to know however how should I change the data format in my .xlsx so that GAUSS recognizes my time variables as such? Thank you for your help.



0



The best format for your date variable is what GAUSS calls DT Scalar format. It is really very easy to use and quite convenient (even if the name at first sounds opaque).

The short answer is to just remove the 'M'. DT Scalar format is just a single value in which the first 4 digits represent the year, the next two represent the month, the next two the day, then hour, minute and second.

So both:

200301

represents January 2003. While:

20030317

represents March 17, 2003--and

200303170823

represents March 17, 2003 at 8:23 am.

Some of the nice things about this format are that it is just numeric data, so you can use all normal matrix operations on it. And GAUSS has a couple of functions that make formatting these very convenient using the dttostr function. For example:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "YYYY-MO");
print str_dts;

will return:

   2003-01 
   2003-04 
   2003-07 
   2003-10 

You can order the date portions of the format string (YYYY, QQ, MO, DD, etc) at any place in the string and add any other string that you would like, for example:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "QQ/YYYY");
print str_dts;

and return:

   Q1/2003 
   Q2/2003 
   Q3/2003 
   Q4/2003 

or even:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "Month MO of the year YYYY");
print str_dts;
Month 01 of the year 2003 
Month 04 of the year 2003 
Month 07 of the year 2003 
Month 10 of the year 2003 

aptech

1,773

Your Answer

3 Answers

1

I don't think that Excel can treat 2003M01 as a date. I think it will instead be a string. So, if you want the dates just for printing, you could read them in as a string with ;xlsReadSA.

myDates = xlsReadSA("myfile.xls", "A2:A3", 1, "");
print myDates;

Assuming that the 'A2' and 'A3' elements were 2003M01 and 2003M02, then the print statement above would return:

2003M01
2003M02
0

Thank you very much for your quick reply.

I would like to know however how should I change the data format in my .xlsx so that GAUSS recognizes my time variables as such? Thank you for your help.

0

The best format for your date variable is what GAUSS calls DT Scalar format. It is really very easy to use and quite convenient (even if the name at first sounds opaque).

The short answer is to just remove the 'M'. DT Scalar format is just a single value in which the first 4 digits represent the year, the next two represent the month, the next two the day, then hour, minute and second.

So both:

200301

represents January 2003. While:

20030317

represents March 17, 2003--and

200303170823

represents March 17, 2003 at 8:23 am.

Some of the nice things about this format are that it is just numeric data, so you can use all normal matrix operations on it. And GAUSS has a couple of functions that make formatting these very convenient using the dttostr function. For example:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "YYYY-MO");
print str_dts;

will return:

   2003-01 
   2003-04 
   2003-07 
   2003-10 

You can order the date portions of the format string (YYYY, QQ, MO, DD, etc) at any place in the string and add any other string that you would like, for example:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "QQ/YYYY");
print str_dts;

and return:

   Q1/2003 
   Q2/2003 
   Q3/2003 
   Q4/2003 

or even:

dts = { 200301, 200304, 200307, 200310 };
str_dts = dttostr(dts, "Month MO of the year YYYY");
print str_dts;
Month 01 of the year 2003 
Month 04 of the year 2003 
Month 07 of the year 2003 
Month 10 of the year 2003 

You must login to post answers.

Have a Specific Question?

Get a real answer from a real person

Need Support?

Get help from our friendly experts.

Try GAUSS for 14 days for FREE

See what GAUSS can do for your data

© Aptech Systems, Inc. All rights reserved.

Privacy Policy