topic Re: macro to perform calculation between using observations that fall between two dates in SAS Programming

macro to perform calculation between using observations that fall between two dates

costasRO — Mon, 23 Oct 2017 14:28:54 GMT

Hi all,

I am trying to write a macro that performs a calculation using observations from a master dataset that fall between two dates.

I have an example below

%macro loop;

/*These variables will be used to create the dates that i want my macro to loop over*/
%do yy = 1986 %to 1987;
%do mm = 2 %to 3;

/*here i create the variables date1 and date2, which specify the length of the window that i want the calculation to occur*/

data _null_;
date2=intnx('month',mdy(&mm,1,&yy),0,'end');
date1= intnx('month', date2, -2,'end');
call symput('date1',date1);
call symput('date2',date2);
run;

/*test is my master data set, in the example below i want to run a regression and keep some output using observations that fall between date1 and date2;

proc reg noprint data=test outest=oreg_ds&yy&mm edf;

where date between &date1 and &date2; *Restricted to DATE1- DATE2 data range in the loop;

model y = x;

run;

%end;
%end;
%mend loop;
%loop;

However, i keep getting errors of the type below, and i am not sure what is going on.

Any help would be much appreciated!

NOTE: Line generated by the macro variable "DATE1".
1 9527
----
22
200

ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, _ALL_, _CHARACTER_,
_CHAR_, _NUMERIC_.

Re: macro to perform calculation between using observations that fall between two dates

LinusH — Mon, 23 Oct 2017 14:36:13 GMT

Use options symbolgen mprint; to monitor macro behaviour.
Also, lift out the date logic outside and test it with hard coded macro variable values.

Re: macro to perform calculation between using observations that fall between two dates

RW9 — Mon, 23 Oct 2017 14:40:51 GMT

Its because here:

call symput('date1',date1);

You are putting the unformatted numeric (which is behind all dates) into the macro variable, use:

call symput('date1',put(date1,date9.));

Although I would really not recommend any of that approach at all. There really are far better and simpler methodologies to attain what you want. For instance consider adding a categorical variable to your dataset:

data have;
  set master.have;
  if 1986 <= year(date) <=1987 and
    month(date) in (2,3) then cat="Year1";
  if ...;
run;

proc reg data=have...
  by cat;
  model...;
run;

I am leaving now, so this was done quickly, but the premise is to manipulate your data to have the category, and then do a by group on the category varaible. This is both simple Base SAS coding (easy to read, and maintain) and uses by grou processing in the reg procedure which is again simpler and easier to use, and will run much quicker. You don't have to use if statements, you could put the do loop in the datastep and categorise the data any way you like, then by group it.

Re: macro to perform calculation between using observations that fall between two dates

ballardw — Mon, 23 Oct 2017 16:58:23 GMT

Try sorting your data by the date variable and then use this:

proc reg noprint data=test outest=oreg_dsedf;.
   by date;
   format date yyqd.;
   model y = x;.
run;.

Which will create one set of output for each calendar quarter. Your Outest data set will also have one record fore each quarter.

At least I think that's what you are attempting to do.

Re: macro to perform calculation between using observations that fall between two dates

costasRO — Thu, 26 Oct 2017 10:51:04 GMT

Thanks RW9 for the reply.

However, i have many dates and by variables so a macro is the only way to go. the code i use below is just a small sample, my data rurns from 1960 to 2017, and i need rolling regressions going back N months for each by variable (maybe around 10K of them) in each month.

I have tried your suggestion below, but i still get errors messages..

Many thanks again