BookmarkSubscribeRSS Feed
BrahmanandaRao
Lapis Lazuli | Level 10

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;

 

3 REPLIES 3
JOL
SAS Employee JOL
SAS Employee

Format should be on a separate statement. The correct syntax for the format statement is:

format ord_date yymmdd10. ;

Tom
Super User Tom
Super User

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.

ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 3 replies
  • 2218 views
  • 0 likes
  • 4 in conversation