SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Export variables as dates to Excel

Posts: 0

Export variables as dates to Excel


I would like to export a sas dataset to Excel, so that the columns will be in date format in Excel. For instance a variable named 01-01-2007 in excel. That way the graphs which I make in Excel can be chosen for different periods by double clicking on the x-axis. However this is not allowed in SAS and I get the following error message when trying to rename a variable to a date:

30 data transp2;
31 set transp;
32 rename _1997=01-01-1997;
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

33 run;

Thank you.
Posts: 8,814

Re: Export variables as dates to Excel

I'm not entirely clear on what it is that you want to do. You are creating a variable or column called "rename_1997" and you want the VALUE of that variable to be a date constant??? specifically Jan 1, 1997???

One of the problems with your assignment statement is that SAS doesn't see any quotes in the assignment it thinks you are trying to assign a VARIABLE named 01-01-1997 as the VALUE for RENAME_1997. The error in this is that a SAS variable name cannot start with a number....hence the error. But, if you did this:
rename_1997 = "01-01-1997";

you would be assigning a text string as the variable value, thus making RENAME_1997 a character variable, not a numeric date/time variable. Instead, you need to tell SAS that you want to create a value that is a numeric SAS date value. If your date of Jan 1, 1997 is going to be the same for every observation, you can do this by:
rename_1997 = "01JAN1997"d;
format mmddyy10.;

...that D after the date string is important -- it tells SAS 2 things: 1) that the variable RENAME_1997 is a numeric variable and 2) that SAS should do a calculation when assigning the value so that the number stored represents the number of days since Jan 1, 1960 (which is the "zero" date in SAS. In fact, this statement would result in the internal value 13515 being stored -- but SAS 'knows' that 13515 is the number of days since Jan 1, 1960 if you use a FORMAT to display the internal number as a date. So, for example, these formats would display the date in the following ways:
SAS format display as
mmddyy10. 01/01/1997
worddate18. January 1, 1997
mmddyyd10. 01-01-1997

If you had done this:
rename_1997 = "15NOV1950"d;
the internally stored value would be -3334

and the formats would display this internal number as:

SAS format display as
mmddyy10. 11/15/1950
worddate18. November 15, 1950
mmddyyd10. 11-15-1950

Now, theoretically, when SAS creates a file for Excel, using PROC EXPORT or the SAS Excel LIBNAME engine, SAS and Excel know how to talk to each other about dates -- for example...SAS has a "zero" date of Jan 1, 1960 while Excel has a "zero" date of Jan 1, 1900. Excel may or may not support or use the SAS format, but generally it does recognize the variable as a date.

Posts: 0

Re: Export variables as dates to Excel


Seems like the goal is to display the column name in excel as excel-standart date instead of _1997. The error you are getting is due to a limitation that sas variable name cannot start with a number.

If you are using SAS 9.2, instead of renaming the variable you can assign a label to it, and then in proc export you can specify that in your excel file you want to display label instead of an actual name
Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation