SAS Add-in for Excel 5.1 - Date formatting issues when using local dataset

Reply
Contributor
Posts: 39

SAS Add-in for Excel 5.1 - Date formatting issues when using local dataset

Hi,

Just wondering if anybody know which date format should be used when using SAS Add-in for Excel 5.1 and a locally stored dataset on a network drive?

I keep getting a message advising that the dataset should be kept on the server, which I am not able to do as Admin has locked down this area of the SAS Server.  I hoped it would be as simple as changing dates to a date9. format... Unfortunately this did not work.  The dates keep defaulting to the year 1960...

Any help would be greatly appreciated.

Many thanks

G

Contributor
Posts: 70

Re: SAS Add-in for Excel 5.1 - Date formatting issues when using local dataset

Hi G,

The last words of your article "The dates keep defaulting to the year 1960" makes me think that your DATE (number of days since 01Jan1960) value might actually be a DATETIME value (number of seconds since 01Jan1960 00:00:00). If this is so, Excel may be interpreting the date portion as zero, hence the 1960.

I just tried this in SAS 9.3 to create a simple permanent dataset:

****************************************************************************************;

*Allocate a Libname                                                                     ;

****************************************************************************************;

Libname SASDATA "C:\Temp";

****************************************************************************************;

*Create a dataset with a DATE value and a DATETIME value.                               ;

****************************************************************************************;

Data SASDATA.MyDates;

MyDate="29Jan1999"d;

MyDateTime=DHMS(MyDate, 18, 15, 20);

Format MyDate Date9.

         MyDateTime DateTime.

        ;

Run;

When I open the dataset in Excel 2010 under SAS Add-In for Microsoft Office 5.1 (5.100.0.12019) (32-bit), I see this:

which is what I am expecting to see. Try the simple DATA step in your own SAS session and see if you get the same results.

If you are still stuck, please post a single value that you see in SAS properly but is not behaving in Excel.

Cheers,

Downunder Dave.

Super Contributor
Posts: 367

Re: SAS Add-in for Excel 5.1 - Date formatting issues when using local dataset

*** What follows is just conjecture - I don't have any inside knowledge of the internals of SAS AMO - but I'll give it my best guess.  Perhaps a little birdie will chime in and correct any errors. ***

The only thing in SAS that makes the number 0 = 01JAN1960 is the associated SAS format.  So, what I think happens with the AMO COM plug in is:  The AMO plugin receives the data value (i.e. 0 for 01JAN1960), as well as metadata describing the data (i.e. the format - note it wouldn't have to be a date format).  The AMO plug in then takes the data value plus the metadata and does the proper conversion of SAS date format to Excel date format.

In particular, SAS and Excel have different epoch dates:  1960-01-01 for SAS and 1900-01-00 for Excel (Dates And Times In Excel).  So, internally the plugin would need to do the conversion of the SAS date to Excel date.

It also uses the metadata to format the data value, with imperfect results.  IOW, I feel there are a few bugs in AMO that will hopefully be resolved in a future release (I've reported these issues to SAS TS).

I did this test:

data sasuser.amo_date_test;

date=date();

datetime=datetime();

date7=date;

date9=date;

b8601da=date;

b8601dn=datetime;

ddmmyy8=date;

ddmmyyd10=date;

dtdate9=datetime;

e8601da=date;

mmddyy6=date;

mmddyyd10=date;

yymmdd8=date;

yymmddb10=date;

yymmddc10=date;

yymmddd10=date;

yymmddn8=date;

yymmddp10=date;

yymmdds10=date;

strdate="25DEC2014";

strdatetime="25DEC2014 12:00:00";

  format

date7     date7.

date9     date9.

b8601da   b8601da.

b8601dn   b8601dn.

ddmmyy8   ddmmyy8.

ddmmyyd10 ddmmyyd10.

dtdate9   dtdate9.

e8601da   e8601da.

mmddyy6   mmddyy6.

mmddyyd10 mmddyys10.

yymmdd8   yymmdd8.

yymmddb10 yymmddb10.

yymmddc10 yymmddc10.

yymmddd10 yymmddd10.

yymmddn8  yymmddn8.

yymmddp10 yymmddp10.

yymmdds10 yymmdds10.

;

run;


I then opened the dataset via AMO using both the workspace server (Sheet1) and direct access to the file in my sasuser library (Sheet2).  The ISO 8601 formats are not recognized by AMO as date formats, so I just get the numeric results.  I was actually surprised that dtdate9 worked ;-) Also, some of the formats, such as date7., are not formatted the same as in SAS.  IMO the plugin should attempt to format the data exactly as the data is displayed in SAS (when possible).  There may be a perfectly valid reason I used date7 instead of another format.  For the date7, ddmmyy8, and other formats, I have to manually go into Excel and change the column formatting to match my SAS formatting.  I can get it to exactly equal the display in SAS; I just have to do it manually.

Can you try running the code above, reading it into Excel via AMO, and let us know your results?  If it works as expected for this dataset, then as has said, check your actual data values in your source dataset.

HTH,

Scott

Ask a Question
Discussion stats
  • 2 replies
  • 603 views
  • 0 likes
  • 3 in conversation