topic Re: Create a Macro for correctting date in SAS Programming

Create a Macro for correctting date

ybz12003 — Mon, 25 Mar 2019 19:21:12 GMT

Hello:

I have a dataset with 10K observations and over 500 variables. In this dataset, many variable (see below) with missing date format are not correct, I would like to correct the date from 01/01/8888 to 88/88/8888. I would like to create a macro to correct all of them. I am curious, is there a way to do that?

%let DateVars = ReportDate AdmitDate DischargeDate CultureDate ERDate;


%macro MissingDateFormat;

       .
       .
       .

%mend;

%MissingDateFormat;

Re: Create a Macro for correctting date

Reeza — Mon, 25 Mar 2019 19:34:08 GMT

Why a macro? Wouldn't an array work just as well and be quicker?

data want;
set have;

array my_dates(*) &dateVars;

do i=1 to dim(my_dates);

if my_dates(i) = '01/01/8888' then my_dates(i) = '88/88/8888';

end;

run;

I'm assuming your dates are characters since those are not valid SAS dates.

@ybz12003 wrote:

Hello:

I have a dataset with 10K observations and over 500 variables. In this dataset, many variable (see below) with missing date format are not correct, I would like to correct the date from 01/01/8888 to 88/88/8888. I would like to create a macro to correct all of them. I am curious, is there a way to do that?
%let DateVars = ReportDate AdmitDate DischargeDate CultureDate ERDate;


%macro MissingDateFormat;

       .
       .
       .

%mend;

%MissingDateFormat;

Re: Create a Macro for correctting date

ballardw — Mon, 25 Mar 2019 20:03:05 GMT

Are these variables supposed to be actual date values? If so your goal will not work as SAS will not accept a day or month number of 88 for either. You could create a custom format that would display a missing, or possibly better a special missing value as '88/88/8888' but you can not set an actual date value to that.

If the value is character then describe how you are using the "date" of 88/88/8888 that would be different than using '01/01/8888'.

Re: Create a Macro for correctting date

ybz12003 — Tue, 26 Mar 2019 12:58:43 GMT

All of the date variables are numeric, not character. They are not the actual date, all of them are the unknown date. Somehow, the system put them as 01/01/8888 instead of 88/88/8888.

Re: Create a Macro for correctting date

ybz12003 — Tue, 26 Mar 2019 13:27:03 GMT

It's strange that I use

if my_dates(i) = '01/01/8888' then my_dates(i) = '88/88/8888';

if my_dates(i) = ' 01/01/8888  then my_dates(i) = 88/88/8888 ;

None of the above could change the date to 88/88/8888. The column attributes show that the variable length is 8 and the format is MMDDYY10.

Re: Create a Macro for correctting date

Reeza — Tue, 26 Mar 2019 14:59:37 GMT

@ybz12003 wrote:

It's strange that I use
if my_dates(i) = '01/01/8888' then my_dates(i) = '88/88/8888';
or
if my_dates(i) = ' 01/01/8888  then my_dates(i) = 88/88/8888 ;
None of the above could change the date to 88/88/8888. The column attributes show that the variable length is 8 and the format is MMDDYY10.

A SAS date literal is specified as 'ddMONyyyy'd or '01Jan2019'd, ergo why it will not work if it's a SAS date.

You cannot have a day or month of 88 so if it is a SAS date that isn't a valid date. You need to change your requirements or store your data as characters which will cause other issues.

Re: Create a Macro for correctting date

Tom — Tue, 26 Mar 2019 15:13:52 GMT

@ybz12003 wrote:

It's strange that I use
if my_dates(i) = '01/01/8888' then my_dates(i) = '88/88/8888';
or
if my_dates(i) = ' 01/01/8888  then my_dates(i) = 88/88/8888 ;
None of the above could change the date to 88/88/8888. The column attributes show that the variable length is 8 and the format is MMDDYY10.

If the variable is of type numeric and has the MMDDYY10. format attached to it then there is no way to store the value you want. The day of the month cannot be 88 and the month of the year cannot be 88.

You could define you own format that displays 01JAN8888 using 88/88/8888 instead of 01/01/8888.

proc format ;
 value mydate 
  '01JAN8888'd = '88/88/8888'
  other=[mmddyy10.]
 ;
run;

Let's try it out:

data test;
  input date yymmdd10. ;
  rawdate=date;
  mydate=date;
  format date mmddyy10. mydate mydate10.;
cards;
8888-01-01
2018-02-04
;
proc print;
run;

Result:

Obs          date    rawdate    mydate

 1     01/01/8888    2530399    88/88/8888
 2     02/04/2018      21219    02/04/2018

Re: Create a Macro for correctting date

ybz12003 — Tue, 26 Mar 2019 15:35:33 GMT

Great! The date format code is WORKING! However, how to change all of them in a bunch of variables? I use the array above. It didn't work.

Re: Create a Macro for correctting date

Tom — Tue, 26 Mar 2019 15:37:15 GMT

@ybz12003 wrote:

Great! The date format code is WORKING! However, how to change all of them in a bunch of variables? I use the array above. It didn't work.

No need for an array.

You want a FORMAT statement.

%let varlist=date1 date2;

proc datasets lib=mylib nolist;
  modify mydsn ;
  format &varlist mydate10. ;
run;
quit;

Re: Create a Macro for correctting date

ballardw — Tue, 26 Mar 2019 15:44:11 GMT

Yet another reason to supply example data in the form of a data step.

With proper example data the first response would likely have covered this issue.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Create a Macro for correctting date

ybz12003 — Tue, 26 Mar 2019 17:21:24 GMT

Amazing! Thanks, Tom. The codes work! One last Q, when I use PROC DATASET, is there a way to output the modified dataset into a new dataset, or it could only use the same name? If not, don't worry about. It's not a big deal.

Re: Create a Macro for correctting date

Tom — Tue, 26 Mar 2019 17:25:26 GMT

If you want to make a new dataset just add the FORMAT statement to the data step.
data new; set old; format &varlist mydate10.; run;

Re: Create a Macro for correctting date

ballardw — Tue, 26 Mar 2019 21:14:37 GMT

@ybz12003 wrote:

Amazing! Thanks, Tom. The codes work! One last Q, when I use PROC DATASET, is there a way to output the modified dataset into a new dataset, or it could only use the same name? If not, don't worry about. It's not a big deal.

Proc datasets will also copy data sets. So try copy and then modify the copy. Or modify the set and then create a copy. Choice is yours. Depends on what you want for contents.