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.));*/
Worked perfectly, thanks! I
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.
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;
Worked perfectly, thanks! I
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.