BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kmardinian
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

  1. figure out how to blank out N_LIST
  2. determine the IF tests, which essentially test whether the current N (or N2) is not yet entered in N_LIST
  3. get the function for counting space-separated word in N_LIST

For #2 and #3 take a look at SAS Functions and Call Routines by Category.  Check the character category

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

7 REPLIES 7
kmardinian
Quartz | Level 8

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!

ballardw
Super User

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.

kmardinian
Quartz | Level 8

Sorry, I seem to have posted this twice, someone was able to direct me in the right direction. Thank you!

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
kmardinian
Quartz | Level 8

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!

mkeintz
PROC Star

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

  1. figure out how to blank out N_LIST
  2. determine the IF tests, which essentially test whether the current N (or N2) is not yet entered in N_LIST
  3. get the function for counting space-separated word in N_LIST

For #2 and #3 take a look at SAS Functions and Call Routines by Category.  Check the character category

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
kmardinian
Quartz | Level 8

Thank you so much, this has been super helpful!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2274 views
  • 0 likes
  • 3 in conversation