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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.