topic Re: SAS Date format in SAS Programming

SAS Date format

jaiganesh — Wed, 19 Jun 2019 12:32:06 GMT

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

Re: SAS Date format

PeterClemmensen — Wed, 19 Jun 2019 12:35:56 GMT

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

Re: SAS Date format

Patrick — Wed, 19 Jun 2019 12:40:51 GMT

@jaiganesh

Add to Proc Import : GETNAMES=YES;

Re: SAS Date format

Kurt_Bremser — Wed, 19 Jun 2019 12:54:59 GMT

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.

Re: SAS Date format

jaiganesh — Wed, 19 Jun 2019 13:49:33 GMT

I've used the same However same output.

Please suggest.

Re: SAS Date format

Tom — Wed, 19 Jun 2019 14:30:06 GMT

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.

Re: SAS Date format

jaiganesh — Wed, 19 Jun 2019 14:41:49 GMT

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....

Re: SAS Date format

Reeza — Wed, 19 Jun 2019 15:25:17 GMT

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.

Re: SAS Date format

jaiganesh — Wed, 19 Jun 2019 16:20:47 GMT

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;

Re: SAS Date format

Reeza — Wed, 19 Jun 2019 17:16:06 GMT

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.

Re: SAS Date format

ghosh — Wed, 19 Jun 2019 19:25:47 GMT

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