Hi Everyone,
For each ID, I want to count the number of positive value for each variable ( a,b ..c) seperately.
Yes, I can do the proc means as below 1 by 1 and sql them back but should it be a better way to get the job done.
Could you please help?
Thank you,
HHC
data have;
input ID a b c d e f g ;
datalines;
1 1 0 -1 6 9 5 3 -9 6
1 2 3 6 0 0 0 -2 -2 -9
1 2 3 6 0 0 0 -2 -2 -9
2 5 0 -1 6 -9 -5 3 9 6
2 4 -3 -6 0 0 0 -2 2 -9
2 -2 3 6 0 0 0 -2 -2 9
;
/*for variable a*/
proc means data=have;
by id;
where a>0;
var a;
output out=want_A(drop=_TYPE_ _FREQ_)
n=N_A_positive;
run;
Or a different approach.
data tab;
set have;
array v(*) a b c d e f g;
do i = 1 to dim(v);
v[i] = (v[i]>0);
end;
drop i;
run;
proc summary data=tab;
var a b c d e f g;
output out=want (drop= _:) sum=;
run;
First transpose the data set then use sql to get positive values for each of the classification.
data want(keep=name val);
set have;
array v(*) a b c d e f g;
do i=1 to dim(v);
val=v(i);
name=vname(v(i));
output;
end;
run;
proc sql;
select name,sum(val>0) as freq_pos from want
group by name;
quit;
Or a different approach.
data tab;
set have;
array v(*) a b c d e f g;
do i = 1 to dim(v);
v[i] = (v[i]>0);
end;
drop i;
run;
proc summary data=tab;
var a b c d e f g;
output out=want (drop= _:) sum=;
run;
WIDE, being the *wrong* dataset format, again...
data have;
input ID a b c d e f g ;
datalines;
1 1 0 -1 6 9 5 3 -9 6
1 2 3 6 0 0 0 -2 -2 -9
1 2 3 6 0 0 0 -2 -2 -9
2 5 0 -1 6 -9 -5 3 9 6
2 4 -3 -6 0 0 0 -2 2 -9
2 -2 3 6 0 0 0 -2 -2 9
;
data have0;
set have;
obs = _n_;
run;
proc transpose data=have0 out=have1;
by ID obs;
var a -- g;
run;
data have2;
set have1;
positive = col1 > 0;
run;
proc sql;
create table want as
select
ID,
_name_ as var,
sum(positive) as N_Positive
from have2
group by ID, var;
quit;
A single DATA step can do the trick:
data want;
array positive {7};
array nums {7} a b c d e f g;
do _n_=1 to 7;
positive{_n_} = 0;
end;
do until (last.id);
set have;
by id;
do _n_=1 to 7;
if nums{_n_} > 0 then positive{_n_} + 1;
end;
end;
do until (last.id);
set have;
by id;
output;
end;
run;
The top loop counts the positives, and the bottom loop outputs the results on each observation for that ID.
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.