topic Re: If Then based on Date format in SAS Programming

If Then based on Date format

uopsouthpaw — Wed, 26 Dec 2018 17:01:55 GMT

Hello all,

I'm attempting to write a macro that will read in Excel data sets (Monthly data) and stack them to a Master data set for trending purposes. I'm using Proc Import, and that reads the format in from Excel. I'm was trying to stay away from using the Data step and manually code all input, format, and informats if possible. The problem I have run into is that the data sets have "Date Opened" saved as one of two different formats (either MMDDYY10. or Datetime16.) as read in by the Proc Import route. Is there a way to do conditional formatting (I don't care for the time portion, only need the date) such as below?

If "the format of [Date Opened] = MMDDYY10. then do;

Year_c = Year(Date Opened);

Month_c = Month(Date Opened);

Year_Month = Cat(Year_c,"_",Month_c);

end;

else if "the format of [Date Opened] = Datetime16. then do;

Year_c = Year(datepart(Date Opened));

Month_c = Month(datepart(Date Opened));

Year_Month = Cat(Year_c,"_",Month_c);

end;

Since [Date Opened] has a length of 8, then I can't separate them by length. I'm attempting not to have to open excel and change the format. Or is essentially the data step my only route.

Thanks,

Wes

Re: If Then based on Date format

PGStats — Wed, 26 Dec 2018 17:14:49 GMT

You can use the VFORMAT function to get the format of a variable.

Re: If Then based on Date format

uopsouthpaw — Wed, 26 Dec 2018 17:22:10 GMT

Thank you for the help.

Re: If Then based on Date format

Tom — Wed, 26 Dec 2018 20:44:51 GMT

A length of 8 just means that you have numeric field. Both DATE and DATETIME values are stored as numbers, but the meaning of the numbers are different. DATE values are days and DATETIME values are seconds.

Depending on the range of dates you are expecting you might try just testing if the number is too large to be a DATE value. For example if you expect to have dates between 1900 and today that would be stored as numbers between -21,914 and 21,544. If you used the DATEPART() function to treat those numbers as number of seconds then you would get a value of -1 or 0 (depending on if the date reas before 1960 or not).

66    data have ;
67       date_opened = date();
68       output ;
69       date_opened = datetime();
70       output;
71    run;

NOTE: The data set WORK.HAVE has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


72
73    data want;
74     set have;
75
76    if datepart(Date_Opened) in (-1,0) then Year_Month = substr(put(Date_Opened,yymmdd10.),1,7);
77    else Year_Month = substr(put(datepart(Date_Opened),yymmdd10.),1,7);
78
79    put (_all_) (=);
80    run;

date_opened=21544 Year_Month=2018-12
date_opened=1861458217.6 Year_Month=2018-12