date format while import the excel file

[BrahmanandaRao]

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;

[JOL]

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

format ord_date yymmdd10. ;

[Tom]

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]

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.