BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mkdmotokats
Fluorite | Level 6

Hi,

I am trying to write a macro for ISO8601 date imputaion. I have submitted the following code. But it is giving me syntax error. Please find me the error. Thanks.

 

Submitted Code

 

%macro dtimpute(var=,vout=,mon=,day=);
data dateimpute;
set dates;
%if &var.^='' %then
%do;
%if %scan(&var.,2,'-')='' %then
%do;
%if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&mon.")||strip("-&day");
%else &vout.=%sysfunc(putn(%scan(&var.,1,'-'),'$4.'))||"-&mon.-"||%sysfunc(putn(%scan(&var.,3,'-'),'$2.'));
%end;
%else %if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&day.");
%else &vout.=&var.;
%end;
run;
%mend dtimpute;
%dtimpute (var=DATE,vout=DATEIMP,mon=06,day=15);

 

 

 

 

LOG

 

87 %dtimpute (var=DATE,vout=DATEIMP,mon=06,day=15);
MLOGIC(DTIMPUTE): Beginning execution.
MLOGIC(DTIMPUTE): Parameter VAR has value DATE
MLOGIC(DTIMPUTE): Parameter VOUT has value DATEIMP
MLOGIC(DTIMPUTE): Parameter MON has value 06
MLOGIC(DTIMPUTE): Parameter DAY has value 15
MPRINT(DTIMPUTE): data dateimpute;
MPRINT(DTIMPUTE): set dates;
SYMBOLGEN: Macro variable VAR resolves to DATE
MLOGIC(DTIMPUTE): %IF condition &var.^='' is TRUE
SYMBOLGEN: Macro variable VAR resolves to DATE
MLOGIC(DTIMPUTE): %IF condition %scan(&var.,2,'-')='' is FALSE
SYMBOLGEN: Macro variable VAR resolves to DATE
MLOGIC(DTIMPUTE): %IF condition %scan(&var.,3,'-')='' is FALSE
SYMBOLGEN: Macro variable VOUT resolves to DATEIMP
SYMBOLGEN: Macro variable VAR resolves to DATE
NOTE: Line generated by the invoked macro "DTIMPUTE".
5 run;
---
22
MPRINT(DTIMPUTE): DATEIMP=DATE run
MLOGIC(DTIMPUTE): Ending execution.
MPRINT(DTIMPUTE): ;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =,
>, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||,
~=.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

 

View solution in original post

18 REPLIES 18
ballardw
Super User

Are you expecting something to be done with the values in the data set dates?

 

The macro language does not understand a data set. It only generates the text you specifiy with macro statements.

There is likely no need for the macro statements %if/%then/%else/%end at all in the shown code. Use the data step versions.

 

You should also investigate the use of CAT, CATT, CATS or CATX function instead of relying on the || concatenation operator. CATS would remove the need for the multiple STRIP function calls.

 

It would help to post code in code boxes in the forum by using the {i} menu and to indent code for readability.

mkdmotokats
Fluorite | Level 6

Thanks for the advice.

What I wanted to this is the following.

There are some partial dates in the datasets, e.g. 2016-10, 2016-  -25 like this so what I want is to be the dates imputed to month of JUNE or 15th day as per month imputation or date imputation. The steps are working without out macro perfectly. But there are more than one variables present in the dataset so I tried do it it with macro. But why that error is coming, I don't understand. Because the given syntax error is coming on the "run;" statement.

 Can you please explain.

 

Thanks.

Astounding
PROC Star

Given that you have code that works for a single variable, you are adding macro language before it is useful.  Starting point:

 

data want;

set have;

* Code that works for a single variable;

run;

 

Next stop:

 

data want;

set have;

array datevars {1} name_of_one_date_variable;

do i=1;

   * Modified code that works for a single variable, use a reference to datevars{i} instead of the variable name;

end;

run;

 

Once that is working, expand the array to include more than one variable.  Creating an array is the usual way to process multiple variables in the same fashion.

 

data want;

set have;

array datevars {5} list of some variable names;

do i=1 to 5;

   * Same modified code .. no changes required;

end;

run;

 

There would be room for macro language after that.  Macro language could handle parameters such as name of the input data set, name of the output data set, list of variable names to process, imputation day=15, imputation month=June.  But get the program working for multiple variables without macro language first.

LinusH
Tourmaline | Level 20
Use MPRINT so can get a better view of the code you are generating.
Data never sleeps
rogerjdeangelis
Barite | Level 11
SAS Forum: Using '%else %do; .. %end;' instead of '%else ...;' - the missing ';'

Code that consumes ';'s

inspired;
https://goo.gl/kEjeMW
https://communities.sas.com/t5/General-SAS-Programming/Macro-giving-Syntax-error-after-compilation/m-p/324603

HAVE
====

Up to 40 obs WORK.DATES total obs=1

Obs    DATE

 1     15-06-2016

WANT ( This what the OPs macro will always does)
==============================================

Up to 40 obs WORK.DATEIMPUTE total obs=1

Obs    DATE          DATEIMP

 1     15-06-2016    15-06-2016

WORKING SOLUTION

 %else &vout.=&var.;

USE

 %else %do; %str(&vout.=&var.;) %end;

Better than

 %else &vout.=&var.;;


I ran my debug macro on your code, only works in the old text editor.
Highlight code and type debug on old text editor command line.

see
https://www.youtube.com/watch?v=JrxooHTx0c8

MPRINT(DEBUGA):   data dateimpute;
822  +set dates;
MPRINT(DEBUGA):   set dates;
823  +DATEIMP=DATE run
                   ---
824  +;
825  +;
MPRINT(DEBUGA):   DATEIMP=DATE run ;
MPRINT(DEBUGA):   ;
826  +run;
MPRINT(DEBUGA):   run;

The problenm is a missing ';' on the last else.
Changes

Could

%else &vout.=&var.;

to

%else &vout.=&var.;;

However I like to use '%else %do;' everywhere for this reason

There are over issues with this code

%if %scan(&var.,2,'-')='' %then
should be
%if %scan(&var.,2,%str(-))= %then

I like (Ian Whitlock did not like this?;
%if "%scan(&var.,2,%str(-))"=""  %then

data dates;
 date='15-06-2016';
run;quit;


%macro dtimpute(var=,vout=,mon=,day=);
data dateimpute;
set dates;
%if &var.^='' %then
%do;
%if %scan(&var.,2,'-')='' %then
%do;
%if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&mon.")||strip("-&day");
%else &vout.=%sysfunc(putn(%scan(&var.,1,'-'),'$4.'))||"-&mon.-"||%sysfunc(putn(%scan(&var.,3,'-'),'$2.'));
%end;
%else %if %scan(&var.,3,'-')='' %then &vout.=strip(&var.)||strip("-&day.");
%else %do; %str(&vout.=&var.;) %end;
run;quit;
%end;
run;
%mend dtimpute;

%dtimpute (var=DATE,vout=DATEIMP,mon=06,day=15);


mkdmotokats
Fluorite | Level 6

I followed your advice and updated the code.

Now the problem is the following.

It seems that the macro is reading only the first value of the variable and judging based on that value, Thats why the output is coming like the following.

 

Date               Dateimpute

2016               2016-06-15

2016-01-12    2016-01-12-06-15

2016-09         2016-09-06-15

 

Please help. Thanks

ballardw
Super User

@mkdmotokats wrote:

I followed your advice and updated the code.

Now the problem is the following.

It seems that the macro is reading only the first value of the variable and judging based on that value, Thats why the output is coming like the following.

 

Date               Dateimpute

2016               2016-06-15

2016-01-12    2016-01-12-06-15

2016-09         2016-09-06-15

 

Please help. Thanks


Time to show what code you are using to generate this input. Otherwise we really do not know which advice you are following or how you implemented it.

 

Reeza
Super User

@mkdmotokats as indicated your problem is not conducive to using a macro. It is better solved with an array, or even a custom function. If you post the WORKING code before you attempted to convert it to a macro we can help.

 

Some examples of issue with your code - you're using macro functions with quotations in the parameters.  If your code nests the DATA and RUN statement within, it isn't going to be generalizable to run for multiple variables without another internal do loop. At this point you're doing what you should be doing in an array, except in a much more long hand and inefficient manner. 

 

Personally, I would take each date and then parse out the year/month/day. If a component was missing then replace it and then create your data from the new vars.  This would be a relatively simple and clean process. 

 

data want;
set have;

year=input(scan(date_var, 1, "-"), 8.);
month=input(scan(date_var, 2, "-"), 8.);
day=input(scan(date_var, 3, "-"), 8.);

if missing(month) then month=6;
if missing(day) then day=15;

new_date=mdy(month, day, year);

run;

And then to apply it to multiple variables, use an array to process it. This would need a number of matching variables to hold the dates.

 

data want;
	set have;
	array old_dates(*) $ date1-date10;
	array new_dates(*) new_date1-new_date10;

	do i=1 to dim(old_dates);
		year=input(scan(old_dates(i), 1, "-"), 8.);
		month=input(scan(old_dates(i), 2, "-"), 8.);
		day=input(scan(old_dates(i) 3, "-"), 8.);

		if missing(month) then
			month=6;

		if missing(day) then
			day=15;
		new_dates(i)=mdy(month, day, year);
	end;
run;
mkdmotokats
Fluorite | Level 6

Hi,

Thank you all for your help.

As you mentioned I am posting the original code without the macro for one variable. It is working perfectly.

data dateimpute;
set dates;
if date^='' then
do;
if scan(date,2,'-')='' then
do;
if scan(date,3,'-')='' then DATEIMP=compress(date||'-'||'06'||'-'||'15');
else DATEIMP=compress(put(scan(date,1,'-'),$4.)||'-'||'06'||'-'||put(scan(date,3,'-'),$2.));
end;
else if scan(date,3,'-')='' then DATEIMP=compress(date||'-'||'15');
else DATEIMP=date;
end;
run;

 

Now what I wanted to do is impute month and day. There are more than one time variable with different imputation criteria. Some are imputed to JUN or some to JAN. Also I have 31 datasets like this.

 

That is why I wanted to use macro. Please help.

 

Thanks 

Patrick
Opal | Level 21

@mkdmotokats

As others already wrote: Do not use macro language for this task.

If you've got already working syntax for a single variable then simply use an array and array processing for multiple variables (=do loop over the array).

 

If you haven't got a solution for a single variable then please post a data step creating a Have dataset and then tell us how the expected result should look like.

mkdmotokats
Fluorite | Level 6

Hi,

As you mentioned dt.PNG

 

The First two columns are the original dates. As I mentioed in above post my working code for single variable. It imputed the DATE variable to DATEIMP correctly. As you see there are more than 1 variable and also I have similar type of 31 datasets. How do I handle this thing.

 

Please help. 

Reeza
Super User

Consider a custom format instead. How are you planning to use later on, does it even have to happen here?

Any commonality for the 31 datasets, do they all have the same variables?

mkdmotokats
Fluorite | Level 6

Only thing datasets have in common is the date variables are in ISO8601 format.

I was planning to have a macro such it takes a input dataset and a date variable and generates output like above.

 

Thanks 

Patrick
Opal | Level 21

@mkdmotokats

It's useful to know that you need something for multiple datasets. But let's approach this step by step, so let's first make this work for a single variable, then genaralize so it works for multiple variables and then generalize again so it works for multiple variables in multiple data sets.

By splitting up the problem into smaller problems, we don't end up with some "massive" macro code where issues could be on any level.

 

People here in this forum are often happy to provide working code. In order to do so, you need to provide actual data and not just a picture, i.e. something like below.

data sample;
  infile datalines truncover dlm=' ';
  input have_str :$10. want_str $10.; 
datalines;
2016 2016-01-01
...more data, ideally representative for all the different date string patterns you're dealing with
;
run;

Looking at the picture you've posted, I don't understand the conversion rules leading to DATEIMP, i.e. why in row 5 DATEIMP becomes 2016-06-15

 

As @Reeza posted, a custom date informat might be the solution - but we need to understand your have data and the conversion rules first.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 1188 views
  • 6 likes
  • 7 in conversation