turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Dealing with Duplicate observations with Proc Freq

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-10-2017 03:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kmardinian

10-10-2017 04:34 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kmardinian

10-10-2017 04:11 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

10-10-2017 04:16 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kmardinian

10-10-2017 04:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

10-10-2017 05:26 PM

Thank you so much, this has been super helpful!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kmardinian

10-10-2017 03:49 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kmardinian

10-10-2017 05:33 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

10-10-2017 06:33 PM

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