SAS Date format

[jaiganesh]

Hello,

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.

Please suggest

[PeterClemmensen]

A SAS variable name con not contain a comma. Try deleting that.

[Patrick]

@jaiganesh 

Add to Proc Import : GETNAMES=YES;  

[jaiganesh]

I've used the same However same output.

Please suggest.

[Kurt_Bremser]

If you want control over the import process, DO NOT use proc import. Save the data to a text file from Excel, and read that with a data step where you set variable names and attributes.

[Tom]

A SAS dataset is not a spreadsheet. 

In particular you cannot use numbers as your variable names.  So SAS is converting the internal value of the cells (the number of days since 1900 with mistaken inclusion of 29FEB1900) as the character representation of that number.  It will do the same thing to date values that you insert into cells in character variables so trying the read the values without columns headers will not help.

To convert the numbers back into dates first convert them from strings into numbers and then subtract the number days between the two different base dates.  Since 1900 is a negative number of days relative to 1960 you just need to do

sasdate=input(excel_date_string,32.)+'30DEC1899'd;

You can then attach your date format of choice to have the numbers appear as dates when printed.

[jaiganesh]

Let me tell you the case very briefly, I have below columns 

Name 43587   43591  43592
Jai       Done    Done    Done;

Now above columns 43587   43591  43592  need to be rename with 01/05/2019 02/05/2019 06/05/2019 respectively...

Kindly suggest....

[Reeza]

Please show a screen shot of your data set after import and the code you've used. I'd recommend a transpose anyways to store your data.

[jaiganesh]

Hello,

I've attached the Screen shot of my data, Please have a look and suggest.

Please find below code i used for it,

proc import out=SAS/SAS_Reports_2019.xlsx"
dbms=xlsx replace;
sheet="SAS_Reports_June19";
getnames=Yes;
run;

[Reeza]

If you switch to labels, I suspect you'll see your dates. Anyways, to solve your problem, the easiest solution is to:

1. Transpose your data to a long format
2. Convert Dates to sAS dates
3. Create a variable with the names you want
4. Do another proc transpose.

Another option is to manually rename the columns.

Rename '493433'n = D2019_06_19;

Try setting:

options validvarname=v7;

Reimport the data after that option and that may help with the names a bit, but not sure.

[ghosh]

options validvarname=any;
libname XL xlsx  "~/dat/Book1.xlsx";
data x;
set xl.sheet1;
run;
proc print;
run;