topic Re: Problem correctly resolving Lag&var in a macro in SAS Programming

Problem correctly resolving Lag&var in a macro

Sharpshooter — Fri, 08 Nov 2013 17:09:20 GMT

Hi,

I'm having real problems correctly creating a value using Lag through a macro.Below is the code and some test data.

I want to lag the response_sum field by the value in the field "for_m3". Using line 14 as an example, I want to resolve to a value of lag2 (which should equal 3426). But instead it puts the value 4001. The log shows this:

MPRINT(LAGIT): response_lag3=lag2(response_sum);

So I would reasonably expect the value in response_lag3 to be 3426 - but it's 4001

data test_data;
   length month 3. response_sum 8. for_m3 8. ;
infile datalines delimiter=',';
   input month response_sum for_m3 ;
   datalines;
1,4877,0
2,8388,0
3,8766,0
4,8491,3
5,6953,3
6,4823,3
7,5761,3
8,7638,3
9,5613,3
10,4258,3
11,4001,3
12,3426,3
13,1414,2
14,1347,2
15,1117,0
16,663,3
;
run;

proc sort test_data;
by month;
run;

%macro lagit(lag_num);
%do b=1 %to &obs_for_macro.;
response_lag&lag_num.=lag&&for_m&lag_num._&b.(response_sum);
%end;

if for_m&lag_num.=0 then
do;
response_lag&lag_num.=0;
end;

%mend ;

data _null_;
set test_data;
by month;
call symput("for_m3_"||left(_n_),compress(for_m3));
if last.month then call symput("obs_for_macro",left(_n_));
run;

%macro L3m();
data test_data_lag;
set test_data;
%lagit(3);

run;
%mend l3m;

%L3m();

Thanks in advance for your help.

Re: Problem correctly resolving Lag&var in a macro

Amir — Fri, 08 Nov 2013 18:25:13 GMT

Hi,

Running your code (after coding the "data=" in the sort) showed:

MPRINT(L3M): data test_data_lag;

MPRINT(L3M): set test_data;

MPRINT(LAGIT): response_lag3=lag0(response_sum);

MPRINT(LAGIT): response_lag3=lag3(response_sum);

MPRINT(LAGIT): response_lag3=lag2(response_sum);

MPRINT(LAGIT): response_lag3=lag0(response_sum);

MPRINT(LAGIT): response_lag3=lag3(response_sum);

MPRINT(LAGIT): if for_m3=0 then do;

MPRINT(LAGIT): response_lag3=0;

MPRINT(LAGIT): end;

MPRINT(L3M): ;

MPRINT(L3M): run;

I assume you just wanted one assignment to response_lag3 for each observation.

Each observation on your input data set passes through all of your data step statements, then the data step outputs the result and moves on to the next observation.

From what is shown, "response_lag3=lag3(response_sum)" is the last assignment used to assign a value to response_lag3, unless the if condition at the end is satisfied. That is why you are seeing 4001.

Regards,

Amir.

Message was edited by: Amir Malik - rephrased last sentence.

Re: Problem correctly resolving Lag&var in a macro

Astounding — Fri, 08 Nov 2013 22:33:32 GMT

Just reading the problem briefly, it looks like you are making this much too complicated. Macro langauge shouldn't be needed. LAG is more complicated than it looks, so just compute the lags and use IF/THEN to select what you need. For example:

back1 = lag(response_sum);

back2 = lag2(response_sum);

back3 = lag3(response_sum);

drop back1 back2 back3;

Then follow up with something like:

if for_m3=3 then lagged_value = back3;

Is your objective really any more complex than that?

Re: Problem correctly resolving Lag&var in a macro

Patrick — Sat, 09 Nov 2013 05:58:55 GMT

Doesn't feel like something you should have to use macro language for. If I've understood correctly what you're after then below code should do.

data test_data;
length month 3. response_sum 8. for_m3 8.;
infile datalines delimiter=',';
input month response_sum for_m3;
datalines;
1,4877,0
2,8388,0
3,8766,0
4,8491,3
5,6953,3
6,4823,3
7,5761,3
8,7638,3
9,5613,3
10,4258,3
11,4001,3
12,3426,3
13,1414,2
14,1347,2
15,1117,0
16,663,3
;
run;

data want;
set test_data;
by month;

_select_row = _n_ - for_m3;
if 1 <= _select_row <= _nobs then
    do;
      set test_data(keep=response_sum rename=(response_sum=response_lag)) point=_select_row nobs=_nobs;
    end;
else call missing(response_lag);
run;

Re: Problem correctly resolving Lag&var in a macro

Tom — Sat, 09 Nov 2013 22:15:31 GMT

Macro is for generating code that is then compiled and run. You cannot use it to reference values of dataset variables during run time. Conditionally selecting a value from a list using the value of another variable as an index is why SAS has the ARRAY construct.

data want ;

set ;

array back (3) _temporary_;

back(1) = lag(response_sum);

back(2) = lag2(response_sum);

back(3) = lag3(response_sum);

if 1 <= for_m3 <=3 then lag_response_sum = back(for_m3);

else lag_response_sum = 0;

run;

response_ lag_response_

Obs month sum for_m3 sum

1 1 4877 0 0

2 2 8388 0 0

3 3 8766 0 0

4 4 8491 3 4877

5 5 6953 3 8388

6 6 4823 3 8766

7 7 5761 3 8491

8 8 7638 3 6953

9 9 5613 3 4823

10 10 4258 3 5761

11 11 4001 3 7638

12 12 3426 3 5613

13 13 1414 2 4001

14 14 1347 2 3426

15 15 1117 0 0

16 16 663 3 1414

Re: Problem correctly resolving Lag&var in a macro

Sharpshooter — Mon, 11 Nov 2013 10:27:40 GMT

Hi all,

Thanks for your quick responses.

I think i have simplified my problem a little too much. In my original post i was useing these 2 variables as an exmaple: "response_sum" and "for_m3". In reality, "response_sum" is 1 of 17 variables i need to create. and "form_m3" is 1 of 4 lag values (lag1, lag2, lag3 and lag12).

The conditional If statement works well for 1 lag for 1 variable but i was looking for something a little more succinct and compact. Here is the extended / complete macro code (apologies for not having the code toe create the test data).

%macro lagit(lag_num);
%do b=1 %to &obs_for_macro.;
mailed_lag&lag_num.=lag&&for_m&lag_num._&b.(mailed_sum);
response_lag&lag_num.=lag&&for_m&lag_num._&b.(response_sum);
conversion_lag&lag_num.=lag&&for_m&lag_num._&b.(conversion_sum);

medium_na_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_NA_sum);
medium_pc_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_PC_sum);
medium_letter_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_Letter_sum);
medium_email_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_Email_sum);
medium_SMS_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_SMS_sum);
medium_Call_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_Call_sum);
medium_Other_lag&lag_num.=lag&&for_m&lag_num._&b.(medium_Other_sum);

gender_male_lag&lag_num.=lag&&for_m&lag_num._&b.(gender_Male_sum);
gender_female_lag&lag_num.=lag&&for_m&lag_num._&b.(gender_Female_sum);
gender_unknown_lag&lag_num.=lag&&for_m&lag_num._&b.(gender_Unknown_sum);

Total_Pack_Cost_lag&lag_num.=lag&&for_m&lag_num._&b.(Total_Pack_Cost);
Conversion_lag&lag_num.=lag&&for_m&lag_num._&b.(Conversion_sum);
%end;
if for_m&lag_num.=0 then
do;
   mailed_lag&lag_num.=0;
   response_lag&lag_num.=0;
   conversion_lag&lag_num.=0;

   medium_na_lag&lag_num.=0;
   medium_pc_lag&lag_num.=0;
   medium_letter_lag&lag_num.=0;
   medium_email_lag&lag_num.=0;
   medium_SMS_lag&lag_num.=0;
   medium_Call_lag&lag_num.=0;
   medium_Other_lag&lag_num.=0;

   gender_male_lag&lag_num.=0;
   gender_female_lag&lag_num.=0;
   gender_unknown_lag&lag_num.=0;

Total_Pack_Cost_lag&lag_num.=0;
Conversion_lag&lag_num.=0;
end;
%mend;

%macro L3m();
data test_data_lag;
set test_data;
%lagit(1);
%lagit(2);
%lagit(3);
%lagit(12);
run;
%mend l3m;

%L3m();

Thanks again...

Re: Problem correctly resolving Lag&var in a macro

Patrick — Mon, 11 Nov 2013 21:16:25 GMT

I believe you could use the code I've previously posted and simply add the additional variables to the rename statement.

As I understand you also in your real data there is still only one variable used to store how many rows backwards relative to the current row you have to read all these "lag" data from. So why not just use this variable and actually go and read the values from this row. In doing so you don't have to create all these lag() variables and you don't need to know in advance all the possible "lags".

Re: Problem correctly resolving Lag&var in a macro

Sharpshooter — Tue, 12 Nov 2013 15:05:44 GMT

Thanks for all the help. Finally settled on the following code:

%macro lag_months(time,var,var_new);
array back_&time._ (12) _temporary_ ;
back_&time._(1)=lag1(&var.);
back_&time._(2)=lag2(&var.);
back_&time._(3)=lag3(&var.);
back_&time._(4)=lag4(&var.);
back_&time._(5)=lag5(&var.);
back_&time._(6)=lag6(&var.);
back_&time._(7)=lag7(&var.);
back_&time._(8)=lag8(&var.);
back_&time._(9)=lag9(&var.);
back_&time._(10)=lag10(&var.);
back_&time._(11)=lag11(&var.);
back_&time._(12)=lag12(&var.);

if 1 <= for_m1 <= 1 then
do;
&var_new._lag1 = back_&time._(for_m1);
end;
else &var_new._lag1 = 0;
if 1 <= for_m2 <= 2 then
do;
&var_new._lag2 = back_&time._(for_m2);
end;
else &var_new._lag2 = 0;
if 1 <= for_m3 <= 3 then
do;
&var_new._lag3 = back_&time._(for_m3);
end;
else &var_new._lag3 = 0;
if 1 <= for_m12 <= 12 then
do;
&var_new._lag12 = back_&time._(for_m12);
end;
else &var_new._lag12 = 0;
%mend;