topic %do loop where the %to value is read from a variable inside the data set. in SAS Programming

%do loop where the %to value is read from a variable inside the data set.

Angela53 — Wed, 06 Feb 2019 02:38:09 GMT

I have a historical dataset organized by "sys," "id" and "y" (year). Each sys/id has a history length varying from 1 to 18 years. I merged variable "count" from proc freq, by sys, tables id. "count" must be read into several %do commands in several macros, either like this:

%do i= 1 %to &s, where &s=(count-1) or &s=count, depending on the calculation being done.

Below is a tiny test data set with analogous structure, plus the first macro of a long series of calculations, all done for each sys/id block of "count" years.

Problem: how do I tell SAS to %do from 1 %to (count-1), where variable "count" is read from the data set and transferred to the macro?

Thanks.

data test; input sys $ ID $ y sub delta1 count;
cards;
a	x	1	9	.	18
a	x	2	9	0	18
a	x	3	8	-1	18
a	x	4	8	0	18
a	x	5	6	-2	18
a	x	6	5	-1	18
a	x	7	4	-1	18
a	x	8	3	-1	18
a	x	9	3	0	18
a	x	10	6	3	18
a	x	11	5	-1	18
a	x	12	4	-1	18
a	x	13	4	0	18
a	x	14	2	-2	18
a	x	15	2	0	18
a	x	16	1	-1	18
a	x	17	0	-1	18
a	x	18	0	0	18
a	y	1	7	.	2
a	y	2	7	0	2
b	z	1	5	.	15
b	z	2	3	-2	15
b	z	3	2	-1	15
b	z	4	9	7	15
b	z	5	9	0	15
b	z	6	9	0	15
b	z	7	8	-1	15
b	z	8	7	-1	15
b	z	9	6	-1	15
b	z	10	5	-1	15
b	z	11	6	1	15
b	z	12	6	0	15
b	z	13	5	-1	15
b	z	14	5	0	15
b	z	15	5	0	15
;
options mprint;
data a; set test; by sys ID y;
%let z=count; 
%macro lags(z);
%let s=%eval(&z.-1);
%do i=1 %to &s;
Lsub&i=lag&i(sub);
%end;
%mend;
%lags(z);run;/*CALL SYMPUT('z',PUT(count,BEST.));*/

Re: %do loop where the %to value is read from a variable inside the data set.

andreas_lds — Wed, 06 Feb 2019 06:40:47 GMT

Do you have a working data-step without macro-code? If not: write that, then post it if you can't see what to change. If you want calculations for each by-group you don't need macro code at all.

Re: %do loop where the %to value is read from a variable inside the data set.

Kurt_Bremser — Wed, 06 Feb 2019 07:16:50 GMT

The macro PREprocessor is for generating dynamic code, NOT for working with data.

Write this down a hundred times.

Could you please supply an example of your expected output? From your data structure, it looks like you want a simple transpose.

Re: %do loop where the %to value is read from a variable inside the data set.

Astounding — Wed, 06 Feb 2019 08:23:10 GMT

Several items need attention. This task should not use macros when a DATA stop will do. And the lag function needs to execute on all observations to produce accurate results.

Let's assume you have examined the data ahead of time and know that the maximum COUNT is 18. Then:

Data a; set test; by sys ID y;

%macro lags;
%do i=1 to 18;
Lsub&i=lag&i(sub);
%end;
%mend;
%lags

Then go back and clean out any values that should be missing. For example,

Array lsub (18) ;
If count < 18 then do k= count + 1 to 18;
Call missing lsub(k);
end;

That's the idea although there is more work to do. You need to wipe out values at the start of each series. When a new SYS begins, LAG captures values that came from the previous SYS, for example.

Re: %do loop where the %to value is read from a variable inside the data set.

s_lassen — Wed, 06 Feb 2019 08:35:43 GMT

The way you define the macro variable will not work. The macro processing is done before the data step is executed, so the macro processor has no knowledge of the value of the datastep variable "Count".

I am not entirely sure what you want to do, but it seems to me that it is easier to use array processing. Is this something like what you want?

proc sql noprint;
  select max(count) into :max trimmed from test;
quit;

data a;
  set test;
  by sys ID y;
  array subs(*) 8 Lsub1-Lsub&max;
  retain Lsub1-Lsub&max;
  if first.ID then
    call missing(of subs(*));
  output;
  do _N_=2 to y;
    subs(_N_)=subs(_N_-1);
    end;
  Lsub1=sub;
run;

Re: %do loop where the %to value is read from a variable inside the data set.

Angela53 — Thu, 07 Feb 2019 16:00:58 GMT

Worked perfectly, thanks! I