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!
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;
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;
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;
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.