SAS Date format convert-CodePudding

I've excel file shown below as input,

Report Name  Address  June14,2019  June 15,2019
Daily                  CH            CJ                  MJ

After i used below syntax to import

proc import out=ds_new datafile="/Sasdata/SAS_Reports_Tracker_2019.xlsx"
dbms=xlsx replace;
sheet="SAS_Reports_June19";
run;

I'm not getting same output as input, I'm getting as

Report Name  Address     43586     43587
Daily                  CH            CJ           MJ

Both Dates (June14,2019 June 15,2019) has converted to SAS internal Date format, However i need exact same output as input.

CodePudding user response：

By default, a SAS variable name cannot contain a comma.
Try using the

However, this is not recommended. You will have trouble at a later stage if you want to perform operations using those two columns. You will have to use "<columns>"n otherwise it will return an error.
Use options validvarname=v7; to change the columns name to a valid SAS name, which are for SAS 7 and later:

Up to 32 mixed-case alphanumeric characters are allowed.
Names must begin with an alphabetic character or an underscore.
Invalid characters are changed to underscores.
Any column name that is not unique when it is normalized is made unique by appending a counter (0, 1, 2, and so on) to the name.

And if you want to see the output with a comma, just use the label option

proc print data=want label;run;

PS: Note that in the example you provide, you specify 5 columns and only fill 4 of them.

CodePudding user response：

When SAS retrieves an Excel DATE value as text instead of numeric you get the raw Excel value converted to text instead of the number as displayed by Excel formats. Since a variable name is by definition character any date values in column headers will be imported in that way.

First transpose the data to get the variable's name into the value of an actual variable.

proc transpose data=ds_new out=ds_tall ;
  by Report Name Address ;
  var '4'n: ;
run;

Then convert the digit string into a date value by converting it to an integer and adjusting for the different base date used to count dates.

data want;
  set ds_tall;
  date = input(_name_,32.)   '30DEC1899'd ;
  format date date9.;
run;