Convert the date into yyyymmdd

[SASuserlot]

I have the date as 3/27/2020 in source. It was reading into sas as '‘43917’. How I can Convert this into 20200327?

[Tom]

---

@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;

[SASuserlot]

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

[Tom]

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

[yabwon]

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

[SASuserlot]

Thank you so much for your  time.

[yabwon · Accepted solution]

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