topic Re: Unique identifiers keeps converting to a date when exporting in SAS Programming

Unique identifiers keeps converting to a date when exporting

erikawashere — Fri, 28 Feb 2020 19:09:15 GMT

I have a file that has unique identifiers that are in the format "nn-nnnn", however, everytime I export the file to CSV or XLSX using proc export, the unique identifiers for some keep converting to a date.

for example: The unique identifier "10-8806" keeps converting to a date "10/1/8806". I've tried formatting the columns in excel to "Text" before exporting and that doesn't seem to work. Is there an option I should be including in the proc export to prevent the identifier from converting to a date?

proc export data=defn
DBMS=XLSX REPLACE LABEL
OUTFILE="C:\Users\roster.XLSX";
SHEET="Roster";
run;

Thanks!

Re: Unique identifiers keeps converting to a date when exporting

ballardw — Fri, 28 Feb 2020 20:26:06 GMT

Blame it on Excel.

It will do strange things with values with dashes. Export to CSV and do NOT open in Excel, use something like Wordpad and you will see the values are fine. If you open in Excel and then save the csv the contents will change.

Changing properties in an XLSX before Export does not have any impact because the Proc Export is replacing the file. That is what the option REPLACE does.

You might try ODS EXCEL as the destination and use Proc Print instead of Export if you must look at it Excel.

data junk;
   x='10-8880';
run;

ods excel file="x:\junk.xlsx";
proc print data=junk;
run;
ods excel close;