I have the date as 3/27/2020 in source. It was reading into sas as '‘43917’. How I can Convert this into 20200327?
What do you mean by "convert"?
Do you want to have it displayed as YYYYMMDD with original date value (see: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/basess/p1m2pok52uqfc3n16ebk28rrm7pl.htm)?
or
Do you want to have it as text string in format "YYYYMMDD" or maybe as a number YYYYMMDD?
For the first do:
data test1;
x = 43917;
format x YYMMDDn8.;
run;
For the second:
data test2;
x = 43917;
y = put(x, YYMMDDn8.);
run;
For the third:
data test3;
x = 43917;
y = year(x)*1e4 + month(x)*100 + day(x);
run;
All the best
Bart
@SASuserlot wrote:
I have the date as 3/27/2020 in source. It was reading into sas as '‘43917’. How I can Convert this into 20200327?
The only way that can happen is if you imported an EXCEL file where the date 27MAR2020 was in a column that also included character strings. In that case SAS will create a character variable and store the date values as digit strings that represent the number that EXCEL uses to store the number.
177 data _null_; 178 date='27MAR2020'd; 179 format date yymmdd10.; 180 days=date; 181 excel=days - '30DEC1899'd ; 182 format days excel comma7.; 183 put (_all_) (=/); 184 run; date=2020-03-27 days=22,001 excel=43,917
So convert the string to a number. Adjust for the difference in base dates used in the date numbering. This will get you a numeric variable with date values (number of days since 1960). You can then apply any type of format that works on date values you want to display the values (SAS does NOT care how the values are displayed, just what the values are.).
data want;
set have;
datevar = input(stringvar , 32.) + '30DEC1899'd ;
format datevar yymmdd10.;
run;
@Tom @yabwon Thank you both and you both are right about having the date with character. Your both solutions worked. However , I have an extension question for you. My excel sheet have the yy variable ( character) and have the following values. 'Not Applicable', 20200327, are character variables where as 43917 came out because somebody entered it in date format in the excel sheet. Is there any way we can control and convert this into 20200327 . Thanks in advance. Greatly appreciate your time.
Don't do data entry in Excel. It is not a database system. It is spreadsheet system that is designed to allow any type of value in any cell.
If you convert all of the values to text in Excel then they will come as text into SAS.
If you want to deal with it afterwards in SAS then check the values and apply the right transformation based on the values.
Perhaps something like:
if stringvar not in (' ','Not Applicable') then do;
datevar = input(stringvar,32.);
if datevar > 19000000 then datevar=input(stringvar,yymmdd10.);
else datevar = datevar +'30DEC1899'd;
end;
format datevar yymmdd10.;
Maybe something like this would work:
data have;
input yy $ 20.;
cards;
Not Applicable
20200327
43917
;
run;
proc print;
run;
data want;
set have;
x = input(yy, ?? best32.);
if x then
do;
if x < 1e7 /* <- !look here! */ then
do;
x = x + '30DEC1899'd;
x = year(x)*1e4 + month(x)*100 + day(x);
end;
end;
run;
proc print;
run;
Bart
Thank you so much for your time.
What do you mean by "convert"?
Do you want to have it displayed as YYYYMMDD with original date value (see: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/basess/p1m2pok52uqfc3n16ebk28rrm7pl.htm)?
or
Do you want to have it as text string in format "YYYYMMDD" or maybe as a number YYYYMMDD?
For the first do:
data test1;
x = 43917;
format x YYMMDDn8.;
run;
For the second:
data test2;
x = 43917;
y = put(x, YYMMDDn8.);
run;
For the third:
data test3;
x = 43917;
y = year(x)*1e4 + month(x)*100 + day(x);
run;
All the best
Bart
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.