topic Re: Help on macro variable in SAS Procedures

Help on macro variable

Siddharth123 — Thu, 29 May 2014 21:45:17 GMT

Hi All,

I have a SAS dataset structured as :

Var1	Var2	Var3
A	.	.
A	.	.
A	.	.
A	1	16APR2010
A	.	.
B	.	.
B	1	17MAY2010
B	.	.
B	.	.

I want to create macro variables : first which counts the distinct number of values in Var1 (2 in this case - A &B) and for each value create a process which reads the data ONLY until Var2 = 1 for each value. Once this is done, then I would like to populate my VAR3 backwards, for example in above case it would be 16APR2010 for A and 17 May2010 for B so in total there should be 6 records in final output file.

Please help.

Kind regards

Re: Help on macro variable

slchen — Fri, 30 May 2014 16:43:55 GMT

data have;
input var1 $ var2 var3 :date9.;
format var3 date9.;
cards;
A    .    .
A    .    .
A    .    .
A    1    16APR2010
A    .    .
B    .    .
B    1    17MAY2010
B    .    .
B    .    .
;
run;

/*Distinct number of values in var1*/

proc sql;

select count(distinct var1) into: num from have;

quit;

%put &num;

data want;
do i=1 by 1 until(last.var1);
    set have;
    by var1;
    retain count;
    if var2=1 then count=i;
end;
do i=1 by 1 until (last.var1);
    set have;
    by var1;
    if i<=count then output;
    end;
format var3 date9.;
drop i count;
run;