I have a dataset that I created from merging two different datasets by ID number. It resembles something like this;
ID N N2
1232 KRAS TIR
1232 KRAS EGF
1111 KRAS MET
1111 EGF PTEN
1111 EGF PTEN
2342 PTEN LKR
2323 ERK MET
2323 MET TER
2222 MET REK
2222 MET MET
Unfortunately, they're are many duplicates of each ID number and N and N2, so my issue is I'd like to find out how many unique observations are there through proc freq. So for ID 1232, it would count KRAS only once and for ID 1111 it would count EGF and PTEN only one as well. Is there anyway to do this through Proc freq? Thank you!
Start out by created N_LIST, without removing duplicates
data want;
set have;
by id notsorted;
length n_list $40;
retain n_list;
if first.id then blank-out-N_LIST
if ?? test of N vs N_list??? then n_list=catx(' ',n_list,N);
if ?? test of N2 vs N_list??? then n_list=catx(' ',n_list,N2);
if last.id;
N_items= some-function-of(N_list);
run;
I leave it to you to
For #2 and #3 take a look at SAS Functions and Call Routines by Category. Check the character category
I have a dataset that I created from merging two different datasets by ID number. It resembles something like this;
ID N N2
1232 KRAS TIR
1232 KRAS EGF
1111 KRAS MET
1111 EGF PTEN
1111 EGF PTEN
2342 PTEN LKR
2323 ERK MET
2323 MET TER
2222 MET REK
2222 MET MET
Unfortunately, they're are many duplicates of each ID number and N and N2, so my issue is I'd like to find out how many unique observations are there through proc freq. So for ID 1232, it would count KRAS only once and for ID 1111 it would count EGF and PTEN only one as well. Is there anyway to do this through Proc freq? Thank you!
Please show what the counts you may be wanting are for that example data.
It sounds like you might be wanting to count occurrences for the same value in different variables but we need to know exactly what you expect.
Sorry, I seem to have posted this twice, someone was able to direct me in the right direction. Thank you!
Assuming the records are grouped by ID, even though not by ascending or descending ID, you can have a DATA step read in your data, BY ID NOTSORTED.
Keep a long character variable N_LIST, which starts out each ID as blank (e.g. at first.id), and then accepts N and N2 values if and only if they are not already in N_LIst, inserting them one at a time as space-separated values (see the CATX function).
At the end of the ID, use the COUNTW function to count the number of space separated words (N_ITEMS) in N_LIST, and output the result with 3 variables: ID N_LIST and N_ITEMS.
Hi mkeintz,
That sounds like it would work perfectly, do you think you could provide me with sample code to see how SAS likes it to be wrriten out?
Thank you!
Start out by created N_LIST, without removing duplicates
data want;
set have;
by id notsorted;
length n_list $40;
retain n_list;
if first.id then blank-out-N_LIST
if ?? test of N vs N_list??? then n_list=catx(' ',n_list,N);
if ?? test of N2 vs N_list??? then n_list=catx(' ',n_list,N2);
if last.id;
N_items= some-function-of(N_list);
run;
I leave it to you to
For #2 and #3 take a look at SAS Functions and Call Routines by Category. Check the character category
Thank you so much, this has been super helpful!
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.