topic Create total number in a sequence in New SAS User

Create total number in a sequence

skhan9 — Mon, 23 Nov 2020 02:21:08 GMT

Hello,

I am trying to create a field for total number in a sequence based on ID and DateCreated. I have already created "SeqNum" based on ID and DateCreated, but now I want to get total number (TotalNum) in that sequence:

ID	DateCreated	SeqNum	TotalNum
1	01mar2020	1	3
1	01mar2020	2	3
1	01mar2020	3	3
2	01apr2020	1	1
2	01sep2020	1	1
3	08aug2020	1	4
3	08aug2020	2	4
3	08aug2020	3	4
3	08aug2020	4	4

I am very new to SAS, so I appreciate any help you can provide. Thank you so much!

Re: Create total number in a sequence

novinosrin — Mon, 23 Nov 2020 02:33:04 GMT



data have;
input ID	DateCreated :date9.	SeqNum;*	TotalNum;
format DateCreated date9.;	
cards;
1	01mar2020	1	3
1	01mar2020	2	3
1	01mar2020	3	3
2	01apr2020	1	1
2	01sep2020	1	1
3	08aug2020	1	4
3	08aug2020	2	4
3	08aug2020	3	4
3	08aug2020	4	4
;

proc sql;
 create table want as
 select *, max(seqnum) as totalnum
 from have
 group by id, DateCreated
 order by id, DateCreated,SeqNum;
quit;

Re: Create total number in a sequence

novinosrin — Mon, 23 Nov 2020 02:36:01 GMT

Or-->


data want;
 do _n_=1 by 1 until(last.id);
   set have;
   by id;
 end;
 totalnum=seqnum;
 do _n_=1 to _n_;
   set have;
   output;
 end;
run;

Re: Create total number in a sequence

mkeintz — Mon, 23 Nov 2020 05:03:18 GMT

The double do until (last.id), as presented by @novinosrin is useful and should be part of every sas programmer's repertoire.

But in this case, simpler code does exactly the same thing. And it offloads more of the logic construction from the programmer to the sas compiler:

data want;
  set have (in=in1) have (in=in2);
  by id;
  if first.id then totalnum=0;
  totalnum+in1;
  if in2;
run;

Re: Create total number in a sequence

skhan9 — Mon, 23 Nov 2020 18:00:16 GMT

Thank you for the quick response and solutions, novinosrin and mkeintz! I tried the option using proc sql and it worked! Really appreciate your help with this!