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

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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
  • 746 views
  • 0 likes
  • 3 in conversation