topic Dealing with Duplicates with Proc Freq in SAS Programming

Dealing with Duplicate observations with Proc Freq

kmardinian — Tue, 10 Oct 2017 19:51:31 GMT

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

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!

Dealing with Duplicates with Proc Freq

kmardinian — Tue, 10 Oct 2017 19:49:56 GMT

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

2342 PTEN LKR

2323 ERK MET

2323 MET TER

2222 MET REK

2222 MET MET

Re: Dealing with Duplicate observations with Proc Freq

mkeintz — Tue, 10 Oct 2017 20:11:24 GMT

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.

Re: Dealing with Duplicate observations with Proc Freq

kmardinian — Tue, 10 Oct 2017 20:16:04 GMT

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!

Re: Dealing with Duplicate observations with Proc Freq

mkeintz — Tue, 10 Oct 2017 20:34:17 GMT

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

figure out how to blank out N_LIST
determine the IF tests, which essentially test whether the current N (or N2) is not yet entered in N_LIST
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

Re: Dealing with Duplicate observations with Proc Freq

kmardinian — Tue, 10 Oct 2017 21:26:13 GMT

Thank you so much, this has been super helpful!

Re: Dealing with Duplicates with Proc Freq

ballardw — Tue, 10 Oct 2017 21:33:04 GMT

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.

Re: Dealing with Duplicates with Proc Freq

kmardinian — Tue, 10 Oct 2017 22:33:14 GMT

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