Hello,
I'm looking to create a number count based on the different IDs and Dataset. My sample dataset and my pursuing result are listed below. Thanks.
data datain;
infile datalines delimiter='/';
input ID : $10. Class : 2.0;
datalines;
THY/1/
THY/1/
THY/3/
THY/3/
THY/3/
THY/3/
THY/5/
OUW/2/
OUW/3/
OUW/3/
OUW/4/
OUW/4/
OUW/4/
OUW/4/
OUW/4/
;
run;
data dataout;
infile datalines delimiter='/';
input ID : $10. Class : 2.0 Count : 2.0;
datalines;
THY/1/2/
THY/3/4/
THY/5/1/
OUW/2/1/
OUW/3/2/
OUW/4/5/
;
run;
proc sql;
create table dataout as
select
id,
class,
count(*) as count
from datain
group by id, class
;
quit;
If the data are already grouped by ID/CLASS, then:
data datain;
infile datalines delimiter='/';
input ID : $10. Class : 2.0;
datalines;
THY/1/
THY/1/
THY/3/
THY/3/
THY/3/
THY/3/
THY/5/
OUW/2/
OUW/3/
OUW/3/
OUW/4/
OUW/4/
OUW/4/
OUW/4/
OUW/4/
;
run;
data want;
set datain;
by id class notsorted;
if last.class;
n=coalesce(dif(_n_),_n_);
run;
The automatic variable _N_ is the iteration number of the data step, i.e. it counts the number of times the code in the data step is executed.
In this case, and in most cases, it is equivalent to the observation number. So the "count" of an ID/CLASS group is just the value of _N_ at the end of a group minus the value of _N_ at the end of the preceding group (calcuated here by the dif function). In the case of the first group the count is just _N_ itself.
proc sql;
create table dataout as
select
id,
class,
count(*) as count
from datain
group by id, class
;
quit;
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.