I have a dataset where the same sample ID is associated with two components (a and b), it also contains singular data (c). I'm trying to add a column that counts the number of occurrences of the ID so I can identify when I only have one of the two components. I'm trying to create the Count column in the table below so I could identify the unpaired component (in red text) using an if then statement (if Comp eq "a" and Count ne 2 then) and (if Comp eq "b" and Count ne 2 then).
Comp | ID | Count |
c | 5236 | 1 |
a | 3298 | 2 |
b | 3298 | 2 |
a | 4172 | 1 |
a | 8723 | 2 |
b | 8723 | 2 |
c | 7692 | 1 |
If it were in Excel, it could be done using the COUNTIF function and dragging it down (=COUNTIF(B:B,B2)) Unfortunately I don't even know where to begin to do this in SAS and haven't had any luck Googling it. I would appreciate any direction you could give me.
In my mind, counting questions are best handled by PROC FREQ.
proc freq data=have;
tables id/noprint out=counts;
run;
data want;
merge have counts(keep=id count);
by id;
run;
You have to sort data set HAVE by ID in order for this to work.
In my mind, counting questions are best handled by PROC FREQ.
proc freq data=have;
tables id/noprint out=counts;
run;
data want;
merge have counts(keep=id count);
by id;
run;
You have to sort data set HAVE by ID in order for this to work.
@PaigeMiller Thank you, that worked great!
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.