DATA Step, Macro, Functions and more

Dealing with Duplicate observations with Proc Freq

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Dealing with Duplicate observations with Proc Freq

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!


Accepted Solutions
Solution
‎10-10-2017 05:26 PM
Trusted Advisor
Posts: 1,022

Re: Dealing with Duplicate observations with Proc Freq

Posted in reply to kmardinian

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,022

Re: Dealing with Duplicate observations with Proc Freq

Posted in reply to kmardinian

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.

Contributor
Posts: 31

Re: Dealing with Duplicate observations with Proc Freq

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!

Solution
‎10-10-2017 05:26 PM
Trusted Advisor
Posts: 1,022

Re: Dealing with Duplicate observations with Proc Freq

Posted in reply to kmardinian

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

Contributor
Posts: 31

Re: Dealing with Duplicate observations with Proc Freq

Thank you so much, this has been super helpful!

Contributor
Posts: 31

Dealing with Duplicates with Proc Freq

Posted in reply to kmardinian

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!

Super User
Posts: 11,343

Re: Dealing with Duplicates with Proc Freq

Posted in reply to kmardinian

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.

Contributor
Posts: 31

Re: Dealing with Duplicates with Proc Freq

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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