Hello,
I have to do conditional count for my dataset.
My sample dataset is listed below:
Item | Q21_01 | Q20_10 | Q20_07 | Q20_04 | Q20_01 | Q19_01 |
abc | up | up | up | up | up | up |
zyx | up | up | down | down | up | up |
hmk | up | down | up | up | down | down |
opq | down | down | down | up | up | up |
Based on this dataset, I need to count movement type by item. At the end I am expecting to get result similar to below:
Item | ||||||||
abc | up | 6 | ||||||
zyx | up | 2 | down | 2 | up | 2 | ||
hmk | up | 1 | down | 1 | up | 2 | down | 2 |
opq | down | 2 | up | 3 |
Note: My final result can take blank value.
Any help would be appreciated.
It will be easier if you transpose the data.
data have;
input (Item Q21_01 Q20_10 Q20_07 Q20_04 Q20_01 Q19_01) ($) ;
cards;
abc up up up up up up
zyx up up down down up up
hmk up down up up down down
opq down down down up up up
;
proc transpose data=have out=tall(rename=(col1=status)) name=period ;
by item notsorted;
var q: ;
run;
data want;
do count=1 by 1 until(last.status);
set tall;
by item status notsorted;
end;
drop period;
run;
Obs count Item status 1 6 abc up 2 2 zyx up 3 2 zyx down 4 2 zyx up 5 1 hmk up 6 1 hmk down 7 2 hmk up 8 2 hmk down 9 3 opq down 10 3 opq up
Thank you for information. It works.
Can we convert it back to thin dataset (transpose again) ?
data have;
input (Item Q21_01 Q20_10 Q20_07 Q20_04 Q20_01 Q19_01) ($) ;
cards;
abc up up up up up up
zyx up up down down up up
hmk up down up up down down
opq down down down up up up
;
proc transpose data=have out=tall(rename=(col1=status)) name=period ;
by item notsorted;
var q: ;
run;
data want;
do count=1 by 1 until(last.status);
set tall;
by item status notsorted;
end;
drop period;
run;
Obs count Item status 1 6 abc up 2 2 zyx up 3 2 zyx down 4 2 zyx up 5 1 hmk up 6 1 hmk down 7 2 hmk up 8 2 hmk down 9 3 opq down 10 3 opq up
It is a little harder to transpose 2 variables. You could call proc transpose twice and then merge the results.
Or just use ARRAYs to collapse. In that case you either need to pick some upper bound for the size of the array or use an extra step to count how many variables you will need.
For example you could just set 5 as the maximum number:
data want;
do index=1 by 1 until(last.item);
do count=1 by 1 until(last.status);
set tall;
by item status notsorted;
end;
array counts [5];
array direction [5] $8 ;
counts[index]=count;
direction[index]=status;
end;
drop period status index count;
run;
Obs Item counts1 counts2 counts3 counts4 counts5 direction1 direction2 direction3 direction4 direction5 1 abc 6 . . . . up 2 zyx 2 2 2 . . up down up 3 hmk 1 1 2 2 . up down up down 4 opq 3 3 . . . down up
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.