Macro giving Syntax error after compilation

[mkdmotokats]

Hi,

Following is one of the variables from a dataset.

data dates;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 
datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016-  -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;

Now the conversion rule is:

1. If you have month missing then impute it by JUNE.

2. If you have day missing then impute it by 15.

3. If you have both month and day missing then impute it by 15th of June.

Following is another variable from same dataset which requires different rule.

data dates2;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 
datalines;
2014,2014-01-10
2016-08,2016-08-10
2015-02-15,2015-02-15
2016-12-12,2016-12-12
2014-01-06,2014-01-06
2015-06-28,2015-06-28
2016-03-31,2016-03-31
2016,2016-01-10
2016,2016-01-10
2015-10,2015-10-10
2016-  -15,2016-01-15
2016-  -20,2016-01-20
2016-12-20,2016-12-20
2016-  -15,2016-01-15
;
run;

Conversion Rule

1. If you have month missing then impute it by January.

2. If you have day missing then impute it by 10.

3. If you have both month and day missing then impute it by 10th of January.

Similarly I have multiple datasets.

I have mentioned my working code for one variable as a reply to @Reeza's post.

Thanks.

[Patrick · Accepted solution]

@mkdmotokats

The following is a bit lengthy as I want to illustrate to you how I would approach such a task so that you don't end up with a complex macro which isn't working but very hard to debug.

1. Make it work for a single variable and a single case using Base SAS code only.

data dates;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 

  /* conversion for a single variable */
  format want_dt_SAS yymmdd10.;
  want_dt_SAS=input(have_date,?? e8601DA.);  
  if missing(want_dt_SAS) then
    do;
      want_dt_SAS=
        mdy(
            coalesce(input(scan(have_date,2,'-'),2.),06),
            coalesce(input(scan(have_date,3,'-'),2.),15),
            input(scan(have_date,1,'-'),4.)
            );
    end;

datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016-  -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;

Now you've got working code onto which you can build. The advantage of first creating such working code is that you now can add modification after modification and run your code after every change. This makes it very easy to spot issues right away and fix them as you go.

2. Make it work for multiple variables and a single case

%let missToMonth=06;
%let missToDay=15;

data dates;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 
  have_date2=have_date;

  /* conversion for multiple variables */
  format want_dt_SAS want_dt2_SAS yymmdd10.;
  array a_haveDate  {*} have_date have_date2;
  array a_wantDate  {*} want_dt_SAS want_dt2_SAS;

  do _i=1 to dim(a_haveDate);
    a_wantDate[_i]=input(a_haveDate[_i],?? e8601DA.);  
    if missing(a_wantDate[_i]) then
      do;
        a_wantDate[_i]=
          mdy(
              coalesce(input(scan(a_haveDate[_i],2,'-'),2.),&missToMonth),
              coalesce(input(scan(a_haveDate[_i],3,'-'),2.),&missToDay),
              input(scan(a_haveDate[_i],1,'-'),4.)
              );
      end;
  end;

datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016-  -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;

3. Macrotize - but still for a single date conversion case

%macro convToSASDate(varList,suffix,missToMonth,missToDay);

 %if %str(&varList)=%str() %then %goto doNothing;
 %if %str(&suffix)=%str() %then %let suffix=_SAS;
 %if %str(&missToMonth)=%str() %then %let missToMonth=06;
 %if %str(&missToDay)=%str() %then %let missToDay=15;


 /* create string with "want" variable names: "have" variable names plus &suffix */
 %local wantVarlist;
 %let wantVarlist=%sysfunc(prxchange(s/(\b\w*\b)/\1&suffix/oi,-1,&varList));

 /* create string with "want" variables */
 format &wantVarlist yymmdd10.;

 /* define arrays for variable lists */
 array a_haveDate {*} &varList;
 array a_wantDate {*} &wantVarlist;

 /* loop over array and convert "have" to "want" */
 do _i=1 to dim(a_haveDate);
 a_wantDate[_i]=input(a_haveDate[_i],?? e8601DA.); 
 if missing(a_wantDate[_i]) then
 do;
 a_wantDate[_i]=
 mdy(
 coalesce(input(scan(a_haveDate[_i],2,'-'),2.),&missToMonth),
 coalesce(input(scan(a_haveDate[_i],3,'-'),2.),&missToDay),
 input(scan(a_haveDate[_i],1,'-'),4.)
 );
 end;
 end;

 drop _i;

 %doNothing:

%mend;

data dates;
 infile datalines truncover dlm=',';
 input have_date :$10. want_date $10.; 
 have_date2=have_date;

 /* conversion for multiple variables */
 %convStrToSASDate(have_date have_date2,_to0615,6,15)

datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016- -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;

4. And now make it further dynamic so that you can call the macro in the same data step multiple times.

The main thing we need to further "macrotize" are the array names as now we have to allow for multiple sets of arrays with different names.

%macro convStrToSASDate(varList,suffix,missToMonth,missToDay);

  %if %str(&varList)=%str() %then %goto doNothing;
  %if %str(&suffix)=%str() %then %let suffix=_SAS;
  %if %str(&missToMonth)=%str() %then %let missToMonth=06;
  %if %str(&missToDay)=%str() %then %let missToDay=15;
  
  %if not %symexist(_arr_counter_x123y) %then %global _arr_counter_x123y;
  %if %str(&_arr_counter_x123y)=%str() %then %let _arr_counter_x123y=0;
  %let _arr_counter_x123y=%eval(&_arr_counter_x123y + 1);

  /* create string with "want" variable names: "have" variable names plus &suffix */
  %local wantVarlist;
  %let wantVarlist=%sysfunc(prxchange(s/(\b\w*\b)/\1&suffix/oi,-1,&varList));

  /* create string with "want" variables */
  format &wantVarlist yymmdd10.;

  /* define arrays for variable lists */
  %let have_arr=a_haveDate_&_arr_counter_x123y;
  %let want_arr=a_wantDate_&_arr_counter_x123y;
  array &have_arr  {*} &varList;
  array &want_arr  {*} &wantVarlist;

  /* loop over array and convert "have" to "want" */
  do _i=1 to dim(&have_arr);
    &want_arr[_i]=input(&have_arr[_i],?? e8601DA.);  
    if missing(&want_arr[_i]) then
      do;
        &want_arr[_i]=
          mdy(
              coalesce(input(scan(&have_arr[_i],2,'-'),2.),&missToMonth),
              coalesce(input(scan(&have_arr[_i],3,'-'),2.),&missToDay),
              input(scan(&have_arr[_i],1,'-'),4.)
              );
      end;
  end;

  drop _i;

  %doNothing:

%mend;

data dates;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 
  have_date2=have_date;
  have_date3=have_date;

  /* conversion for multiple variables */
  %convStrToSASDate(have_date have_date2,_to0615,6,15)
  %convStrToSASDate(have_date3,_to0101,1,1)

datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016-  -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;

You end up with a macro which works, but which is already on a complexity level where it will take a bit of effort should you ever have to maintain it later on when you don't remember all the details how you got there anymore.

So how far do you want to take it? How often do you really need such a conversion?

Instead of going for such a macro approach, you could also implement the pure date conversion logic in a function (using Proc FCM) or have only this bit in a macro and then code all the rest via data step (=all the array definitions and the looping). 

That's more copy/paste of code but it's much easier to read and maintain.

If your starting point are external files which you first need to read via an input statement, then a neat way would be to implement this conversion via Proc FCMP and then use this function in a user defined informat. This way you could read the source strings directly and convert them to SAS Date values via an Informat in the Input statement.

[mkdmotokats]

@Patrick Thank you very much for you help.

[Patrick]

@mkdmotokats

Thinking a bit further: One would normally use array processing for applying the same processes to multiple variables in order to type less code and keep things more compact and easier to maintain. But as we're generating this code with a macro it's may be more important to keep the macro simple and it's no issue to generate very similar Base SAS code blocks over and over again.

Following such an approach below should do (my preferred version):

%macro convStrToSASDate(varList,suffix,missToMonth,missToDay);

  %if %str(&varList)=%str() %then %goto doNothing;
  %if %str(&suffix)=%str() %then %let suffix=_SAS;
  %if %str(&missToMonth)=%str() %then %let missToMonth=06;
  %if %str(&missToDay)=%str() %then %let missToDay=15;
  
  /* create conversion Base SAS code for each variable name in &varList */
  %local i have_var want_var;
  %let i=1;

  %do %while (%scan(&varList,&i) ne );
    %let have_var=%scan(&varList,&i);
    %let want_var=&have_var.&suffix;
    
    format &want_var yymmdd10.;
    &want_var=
      mdy(
          coalesce(input(scan(&have_var,2,'-'),2.),&missToMonth),
          coalesce(input(scan(&have_var,3,'-'),2.),&missToDay),
          input(scan(&have_var,1,'-'),4.)
          );

    %let i=%eval(&i + 1);
  %end;

  %doNothing:

%mend;

data dates;
  infile datalines truncover dlm=',';
  input have_date :$10. want_date $10.; 
  have_date2=have_date;
  have_date3=have_date;

  /* conversion for multiple variables */
  %convStrToSASDate(have_date have_date2,_to0615,6,15)
  %convStrToSASDate(have_date3,_to0101,1,1)

datalines;
2016-05-12,2016-05-12
2015-06-28,2015-06-28
2016-02-12,2016-02-12
2016-03,2016-03-15
2016,2016-06-15
2015,2015-06-15
2015-02,2015-02-15
2014-09,2014-09-15
2016-03-31,2016-03-31
2015-11-12,2015-11-12
2016-  -15,2016-06-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
2016-10-15,2016-10-15
;
run;