topic Re: enumerate rows by group by only on condition in SAS Programming

enumerate rows by group by only on condition

Nasser_DRMCP — Mon, 07 Dec 2020 16:40:31 GMT



data have;
input client date date9. status $ ;
format date date9.;
datalines ;
123 10NOV2020 A
123 10NOV2020 S
123 12NOV2020 A
123 12NOV2020 S
456 10NOV2020 A
456 10NOV2020 S
456 12NOV2020 S
456 12NOV2020 S
;


data want ;
set have ;
by client date ;
if first.date then cpt=1 ; else cpt +1 ;
run ;

Hello,

this code enumerate the rows by client/date group whatever the status.

I would like to enumerate rows only if status = S

thanks in advance for your help

regard

Nasser

Re: enumerate rows by group by only on condition

ballardw — Mon, 07 Dec 2020 16:48:39 GMT

It might help to show what you expect for output.

At least two easy ways:

data want ; set have ; where status='S'; by client date ; if first.date then cpt=1 ; else cpt +1 ; run ;

proc freq data=have;
    where status='S';
   tables client*date / list ;
run;

Depending on what you mean by "enumerate". The first adds a counter. The second counts occurrences. The first requires your Have data to be sorted by client and date, the second doesn't.

Re: enumerate rows by group by only on condition

Nasser_DRMCP — Mon, 07 Dec 2020 16:55:36 GMT

thanks Ballardw

Here is the result I would like to get

client	date	status	needed	rules
123	10Nov2020	A
123	10Nov2020	S	1	first S of 10nov
123	12Nov2020	A
123	12Nov2020	S	1	first s of 12nov
456	10Nov2020	A
456	10Nov2020	S	1	first s of 10 nov
456	12Nov2020	S	1	first s of 12 nov
456	12Nov2020	S	2	2nd S of 12nov

Re: enumerate rows by group by only on condition

ballardw — Mon, 07 Dec 2020 17:13:50 GMT

Perhaps

proc sort data=have;
  by client date status;

data want ;
set have ;
by client date status ;
if first.status and status='S' then cpt=1 ; 
   else if status='S' then cpt +1 ;
   else if status ne 'S' then cpt=.;
run ;

Re: enumerate rows by group by only on condition

Tom — Mon, 07 Dec 2020 17:22:15 GMT

I would use zero instead of missing, but that is easy to change.

data want ;
  set have ;
  by client date ;
  if first.date then needed=.;
  if status='S' then needed +1 ;
run ;

Re: enumerate rows by group by only on condition

Astounding — Mon, 07 Dec 2020 17:20:47 GMT

Here's a variation that gets what you asked for. It doesn't change the order of the observations (which is also what you asked for).

data want ;
set have ;
by client date ;
if first.date then count=0 ; 
if status='S' then do;
    count +1 ;
    cpt = count;
end;
drop count;
run ;