topic Re: conditional count in SAS Programming

conditional count

dht115 — Wed, 24 Mar 2021 18:37:15 GMT

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.

Re: conditional count

Tom — Wed, 24 Mar 2021 19:06:24 GMT

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

Re: conditional count

dht115 — Wed, 24 Mar 2021 20:56:46 GMT

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

Re: conditional count

Tom — Wed, 24 Mar 2021 21:30:18 GMT

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