Hi Guys,
How to change date format using proc import for excel file
ord_no | purch_amt | ord_date | custom_id | salesman_id |
70001 | 150.5 | 20121005 | 3005 | 5002 |
70009 | 270.65 | 20120910 | 3001 | 5005 |
70002 | 65.26 | 20121005 | 3002 | 5001 |
70004 | 110.5 | 20120817 | 3009 | 5003 |
proc import datafile='/home/u35263349/orders.xlsx'
out=order_table
dbms=xlsx
DBSASTYPE (ord_date) = (FORMAT = yymmdd10.);
run;
Format should be on a separate statement. The correct syntax for the format statement is:
format ord_date yymmdd10. ;
PROC IMPORT is going to create the type and length of the variables based on what is in the cells in the column of the XLSX file.
If the column only has DATE values then it will create a numeric variable with data values.
So then just add a step to change the format used to display the date values.
proc datasets lib=WORK nolist;
modify ORDER_TABLE;
format ord_date yymmdd10.;
run;
quit;
If you know the NAME of the first SHEET in the XLSX workbook then you could skip PROC IMPORT and use the XLSX libname engine instead.
libname myxlsx xlsx '/home/u35263349/orders.xlsx';
data order_table;
set myxlsx.SHEET1 ;
format ord_date yymmdd10.;
run;
And if (as is not uncommon with XLSX files) the column has a mix of numeric and character cells or just has those digits strings you showed as numbers or character strings then SAS will not make the variable a DATE value and you will have to add a data step to convert what ever it did create into date values before you can attach a date type format to it.
I would start with a bare import and then look at the results of Proc Content for the properties of the data set. Because of the many questionable things I see done in spreadsheet files it is quite possible that the "ord_date" isn't even a data value in the file, just a number impersonating a date because people see it that way.
So try running:
proc import datafile='/home/u35263349/orders.xlsx' out=order_table dbms=xlsx ; run; proc contents data=order_table; run;
Then share the results of the Proc Contents output.
Hint: if you see that your Ord_date is numeric with a format like BEST12. then the value is not a date and you need to do more get one. Not hard, one line of code in a data step to create and another to assign a format, just routine.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.