topic Re: Macro help: Reuse lines in multiple programs in SAS Programming

Macro help: Reuse lines in multiple programs

coladuck — Fri, 13 Mar 2020 16:29:16 GMT

Hi, we'd like to store frequently used lines of programs in macro. For example:

%macro ownerrange; 
Length owner $100.;
if ((ID='123' and &EVENT_DATE > '01APR2011'D) or (ID='456' and &EVENT_DATE > '05JUN2015'D) or (ID='789')) 
then owner = 'JOHN'; 

%mend; 


data orders; 
length ID $3 cost 8. date1 8. date2 8.; 
input ID cost date1 date2; 
informat date1 mmddyy10. date2 mmddyy10.;
format date1 mmddyy10. date2 mmddyy10.; 
datalines; 
123 50 01/01/2020 02/01/2019
123 60 01/01/2011 03/05/2020
456 10 04/01/2019 01/03/2020
; 
run; 

%let EVENT_DATE=date1; 

data owner; 
set orders; 

%ownerrange

run;

For some reason my %ownerrange won't work. It gives me:

ERROR 180-322: Statement is not valid or it is used out of proper order.

But if I directly replace it with the code written in the macro, it works no problem. Anything I missed?

After that, we'd like to extract the dates and ownership info and store it in dataset. The thing I can think of is do something like this below and use &runlist in program. but it won't work. Any suggestion is welcomed! Hope I described my question clear enough. Thank you!!

proc sql;
  select case when ID^='' and date1 ^=. and date2=. then 
cats('IF ID=',id,' AND &EVENT_DATE >= ',date1,')') 
         ... end
     into :runlist
     from dataset_withdatesowner
	 ; 
quit;

Re: Macro help: Reuse lines in multiple programs

Reeza — Fri, 13 Mar 2020 16:38:44 GMT

Post your log using the debugging options:

options mprint symbolgen;

Run your code and get the log and see if you can find the issue. If you cannot, post the log.

Re: Macro help: Reuse lines in multiple programs

ballardw — Fri, 13 Mar 2020 16:44:35 GMT

Copy the log including the definition of the macro, the data steps and the code that calls the macro. Copy all that from the log. Then open a code box on the forum and paste the result.

I can't duplicate your error with your code:

365  %macro ownerrange;
366  Length owner $100.;
367  if ((ID='123' and &EVENT_DATE > '01APR2011'D) or (ID='456' and &EVENT_DATE > '05JUN2015'D) or
367!  (ID='789'))
368  then owner = 'JOHN';
369
370  %mend;
NOTE: The macro OWNERRANGE completed compilation without errors.
      6 instructions 212 bytes.
371
372
373  data work.orders;
374  length ID $3 cost 8. date1 8. date2 8.;
375  input ID cost date1 date2;
376  informat date1 mmddyy10. date2 mmddyy10.;
377  format date1 mmddyy10. date2 mmddyy10.;
378  datalines;

NOTE: The data set WORK.ORDERS has 3 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


382  ;
383  run;
384
385  %let EVENT_DATE=date1;
386
387  data work.owner;
388  set work.orders;
389
390  %ownerrange
391
392  run;

NOTE: There were 3 observations read from the data set WORK.ORDERS.
NOTE: The data set WORK.OWNER has 3 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

In general when using a macro and generating errors on of the firs things to do is to clear the log, set OPTIONS MPRINT; and rerun the code to see what the macro generated.

Re: Macro help: Reuse lines in multiple programs

coladuck — Wed, 18 Mar 2020 17:10:58 GMT

You're right. It does not give me error this time when I restarted my EG. Very strange...

Another question, is it possible that I put everything I wrote in the macro into a macro variable using

%let = "", and just refer to the macro variable in the program?

I tried:

%let programlines = "Length owner $100.;
if ((ID='123' and &EVENT_DATE > '01APR2011'D) or (ID='456' and &EVENT_DATE > '05JUN2015'D) or (ID='789')) 
then owner = 'JOHN';"; 

data orders; 
length ID $3 cost 8. date1 8. date2 8.; 
input ID cost date1 date2; 
informat date1 mmddyy10. date2 mmddyy10.;
format date1 mmddyy10. date2 mmddyy10.; 
datalines; 
123 50 01/01/2020 02/01/2019
123 60 01/01/2011 03/05/2020
456 10 04/01/2019 01/03/2020
; 
run; 

%let EVENT_DATE=date1; 

data owner; 
set orders; 

&programlines; 

run;

and of course, it does not work. It there a way to just use macro variables?

I ask because in the end, all the dates and owner information will be stored in a dataset and I'd like to use something like this below to generate lines automatically:

Proc sql; 
select cat('if ID=', ID, 'and &eventdate>', datevariable, 'then owner="', ownervariable,'"; output;') 
into: programlines
from datacontainvalues
; 
quit;

Thank you!!

Re: Macro help: Reuse lines in multiple programs

ghosh — Wed, 18 Mar 2020 17:46:40 GMT

This line is not valid outside data step

%let EVENT_DATE=date1;

Re: Macro help: Reuse lines in multiple programs

ghosh — Wed, 18 Mar 2020 18:01:16 GMT

You are using the macro variable incorrectly. In fact you do't need it

%macro ownerrange;
	Length owner $100.;
	if ((ID='123' and date1 > '01APR2011'D) or (ID='456' and date1 > '05JUN2015'D) 
		or (ID='789')) then owner='JOHN';
%mend;

data orders;
	length ID $3 cost 8. date1 8. date2 8.;
	input ID cost date1 date2;
	informat date1 mmddyy10. date2 mmddyy10.;
	format date1 mmddyy10. date2 mmddyy10.;
	datalines;
123 50 01/01/2020 02/01/2019
123 60 01/01/2011 03/05/2020
456 10 04/01/2019 01/03/2020
;
run;

data owner;
	set orders;
	%ownerrange ;
	run;

Re: Macro help: Reuse lines in multiple programs

Tom — Wed, 18 Mar 2020 18:14:08 GMT

Yes, you can put code into a macro variable. Just make sure you understand how to get it in and how to evaluate it in a way that it generates the code you want.

One obvious problem is trying to get the semi-colons into the macro variable. Adding the quotes like you did will allow that. But then you have to remember to remove them so they don't become part of the program you are tying to generate.

%sysfunc(dequote(&programlines))

Another issue the timing of when you are changing the value of the EVENT_DATE macro variable. If you use the %LET statement you posted:

%let programlines = 
"Length owner $100; 
if ((ID='123' and &EVENT_DATE > '01APR2011'D)
 or (ID='456' and &EVENT_DATE > '05JUN2015'D)
 or (ID='789')) then owner = 'JOHN';
";

Then &EVENT_DATE will be evaluated when the macro variable is created and your later attempt to change its value will not have any impact. You could use single quotes on the outside instead to prevent the macro processor from trying to process the text. Make sure to also either change the single quotes on the inside to double quotes, or just double them up.

%let programlines = 
'Length owner $100; 
if ((ID="123" and &EVENT_DATE > "01APR2011"D)
 or (ID="456" and &EVENT_DATE > "05JUN2015"D)
 or (ID="789")) then owner = "JOHN";
';

Then when SAS sees the unquoted value of the macro the macro processor will see the & and evaluate it to the current value of EVENT_DATE.

Re: Macro help: Reuse lines in multiple programs

Tom — Wed, 18 Mar 2020 23:24:04 GMT

Your example looks find. Please post the SAS log from the run that generated errors.

Note that it is not a good idea to create a macro that references "magic" macro variables. Macro variables that are NOT defined as input parameters nor explicitly declared as LOCAL or GLOBAL. Like the EVENT_DATE reference in your macro definition. If you need that parameter then It would be much better to define that as a parameter to the macro.

%macro ownerrange(event_date); 
length owner $100;
if ((ID='123' and &EVENT_DATE > '01APR2011'D)
 or (ID='456' and &EVENT_DATE > '05JUN2015'D)
 or (ID='789')) then owner = 'JOHN'
; 
%mend;

Then supply the actual variable name when you call the macro.

data owner; 
  set orders; 
  %ownerrange(date1)
run;

You might have trouble with the LENGTH statement if OWNER already exists in the incoming dataset. You cannot change the length of character variable after it has already been defined. PS There is no need to include a decimal point in the numbers used in a length statement, they can only be integer values.