BookmarkSubscribeRSS Feed
SAS_MAN_MN
Calcite | Level 5

I have an issue on SAS 9.4, using proc export.

The dataset to be exported, has a date field which is not exporting correctly.

 

For all records where the value of the date field in the original file is <= 12/31/1959,

then the value of that field in the exported XLSX file, is 12/31/1959.

 

I have changed the values in that column by hand before exporting to check,

and found that 12/31/1959 is the boundary line between exporting correctly and not.

 

To make sure the problem wasn't (say) corrupted data, I also tried adding 20 years

to each value in the date field using INTX, before exporting, so that all values were > 12-31-1959,

and all the new values were exported to .XLSX correctly.

 

The syntax of the export is 

Proc Export data=work.dataset

outfile  = "/mydir/outfile.xlsx"

dbms=xlsx

replace label;

run;

 

Incidentally, this issue occurs when the field in the exported file is in DDMMYY10. format

(which fulfills a business rule), but does *NOT* occur when the field is in DATE9. format.

For DATE9, correctly exported values of 01/01/1959 in the SAS dataset, 

become '1-Jan-59' in the exported .XLSX file.

 

Is this is known issue, or is there a workaround for it?

 

2 REPLIES 2
PaigeMiller
Diamond | Level 26

For all records where the value of the date field in the original file is <= 12/31/1959, then the value of that field in the exported XLSX file, is 12/31/1959.

 

I am not experiencing this. I get the expected output in Excel.

 

data a;
    date='02JAN1929'd;
    format date ddmmyy10.;
run;
proc export data=a outfile="test.xlsx" dbms=excel replace;
run;
--
Paige Miller
SASKiwi
PROC Star

Looks like you might be getting this problem: 58526 - The ddmmyy10. format writes different values to different sheets when you are using the XLSX...

 

What SAS maintenance release are you using?

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 772 views
  • 0 likes
  • 3 in conversation