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

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Frequencies not adding up

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 01-13-2017 12:10 PM
(1639 views)

I'm trying to find the frequencies for only unique ID numbers. I tried PROC FREQ, but couldn't figure out how to do whatever the SAS equivilant of SELECT DISTINCT is. I ran the following code and got numebrs that don't add up.

Code:

PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n;

Result:

20599 |

Code:

PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '1a (obs): Demonstrating knowledge of content and pedagogy'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '1a (p&p): Demonstrating knowledge of content and pedagogy'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '1e (obs): Designing coherent instruction'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '1e (p&p): Designing coherent instruction'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '2a: Creating an environment of respect and rapport'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '2d: Managing student behavior'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '3b: Using questioning and discussion techniques'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '3c: Engaging students in learning'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '3d: Using assessment in instruction'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '4e (obs): Growing and developing professionally'; PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n WHERE MOTPComponentDescription = '4e (p&p): Growing and developing professionally';

Result:

1a (obs): Demonstrating knowledge of content and pedagogy: 700

1a (p&p): Demonstrating knowledge of content and pedagogy: 606

1e (obs): Designing coherent instruction: 15622

1e (p&p): Designing coherent instruction: 1135

2a: Creating an environment of respect and rapport: 2466

2d: Managing student behavior: 1005

3b: Using questioning and discussion techniques: 808

3c: Engaging students in learning: 2516

3d: Using assessment in instruction: 3058

4e (obs): Growing and developing professionally: 5245

4e (p&p): Growing and developing professionally: 588

SUM = 33746

33746 != 20599

My data set looks like this (had to x out PII):

MOTPID | MOTPComponentDescription | SchoolDBN | EmployeeID | FirstName | LastName | EvaluatorUserID | EvaluatorName | Comments | complev_flag_ct | sentence_count | percent_similar_sentences | Rating | MOTPDate | Y16_Overall_Rating |

1051875 | 1e (obs): Designing coherent instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 3 | 0.00% | N/A | 9/13/2016 0:00 | Ineffective | |

1051875 | 1e (obs): Designing coherent instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 3 | 0.00% | N/A | 9/13/2016 0:00 | Ineffective | |

1051875 | 1e (obs): Designing coherent instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 3 | 0.00% | N/A | 9/13/2016 0:00 | Ineffective | |

1051876 | 3d: Using assessment in instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 4 | 0.00% | N/A | 9/13/2016 0:00 | Developing | |

1051876 | 3d: Using assessment in instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 4 | 0.00% | N/A | 9/13/2016 0:00 | Developing | |

1051876 | 3d: Using assessment in instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 4 | 0.00% | N/A | 9/13/2016 0:00 | Developing | |

1051876 | 3d: Using assessment in instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 4 | 0.00% | N/A | 9/13/2016 0:00 | Developing | |

1051876 | 2a: Creating an environment of respect and rapport | 13K527 | xxx | xxx | xxx | xxx | xxx | 1 | 5 | 20.00% | N/A | 9/13/2016 0:00 | Developing | |

1051878 | 1e (obs): Designing coherent instruction | 27Q400 | xxx | xxx | xxx | xxx | xxx | 0 | 6 | 0.00% | N/A | 9/13/2016 0:00 | Effective | |

1051878 | 1e (obs): Designing coherent instruction | 27Q400 | xxx | xxx | xxx | xxx | xxx | 0 | 6 | 0.00% | N/A | 9/13/2016 0:00 | Effective | |

1051878 | 1e (obs): Designing coherent instruction | 27Q400 | xxx | xxx | xxx | xxx | xxx | 0 | 6 | 0.00% | N/A | 9/13/2016 0:00 | Effective | |

1051886 | 1e (obs): Designing coherent instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 6 | 0.00% | N/A | 9/14/2016 0:00 | Developing | |

1051886 | 1e (obs): Designing coherent instruction | 13K527 | xxx | xxx | xxx | xxx | xxx | 0 | 6 | 0.00% | N/A | 9/14/2016 0:00 | Developing | |

**Looking for any ideas on what went wrong or if there's a better way to get my desired result (the count of unique MOTPID's by MOTPCopmponentDescription. Thanks so much in advance!**

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks so much. Maybe my post was unclear. I'm not interested in how many MOTPID's had 1, 2, or 3 observations, but rather for a given MOTPComponentDescription how many unique MOTPIDs are there.

Either way, I found a solution:

```
data comment_analysis;
set WORK.'0__1_MOTP_COMMENTS_0001'n;
run;
proc sort data=comment_analysis nodupkey out=comment_analysis_clean dupout=dups;
by motpid;
run;
proc freq data=comment_analysis_clean;
table MOTPComponentDescription;
run;
```

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

what does

proc freq data= WORK.'0__1_MOTP_COMMENTS_0000'n;

tables MOTPID*MOTPComponentDescription /list missing;

run;

give you?

I suspect the issue you see comes from the way you are using distinct.

What about

PROC SQL;

SELECT MOTPID, MOTPComponentDescription , count(*) as count

FROM WORK.'0__1_MOTP_COMMENTS_0000'n

group byMOTPID, MOTPComponentDescription;

quit;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Here's a set of two PROC FREQs that (together) give what you asked plus more. I'm not sure if you're interested in the "plus more" part but you might be.

proc freq data=have noprint;

tables MOTPComponentDescription * MOTPID / out=summarized_counts;

run;

proc freq data=summarized_counts;

tables MOTPComponentDescription * Count / missing list;

run;

This describes your original data set as follows. For each MOTPComponentDescription, how many MOTPIDs had a single observation? (That's the part you asked for.) How many have 2 observations? How many have 3 observations? It gives you that summary for whatever counts actually appear in the data set.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks so much. Maybe my post was unclear. I'm not interested in how many MOTPID's had 1, 2, or 3 observations, but rather for a given MOTPComponentDescription how many unique MOTPIDs are there.

Either way, I found a solution:

```
data comment_analysis;
set WORK.'0__1_MOTP_COMMENTS_0001'n;
run;
proc sort data=comment_analysis nodupkey out=comment_analysis_clean dupout=dups;
by motpid;
run;
proc freq data=comment_analysis_clean;
table MOTPComponentDescription;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You can also simplify your SQL query.

```
PROC SQL;
SELECT MOTPComponentDescription, COUNT (DISTINCT MOTPID) as count
FROM WORK.'0__1_MOTP_COMMENTS_0000'n
group by MOTPComponentDescription;
quit;
```

EDIT: unless you have an explicit exclusion criteria, ie a MOTPID can only be per one MOTPComponentDescription then the total doesn't have to add up to the distinct count you found earlier. You can have a single MOTPID across different MOTPComponentDescription, which means the sum can be greater than the overall unique count.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks so much for all of your responses. I managed to figure out the following solution, which got the exact result I was looking for:

```
data comment_analysis;
set WORK.'0__1_MOTP_COMMENTS_0001'n;
run;
proc sort data=comment_analysis nodupkey out=comment_analysis_clean dupout=dups;
by motpid;
run;
proc freq data=comment_analysis_clean;
table MOTPComponentDescription;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@LizGagne Do note that the SQL solution is the same answer, without multiple steps.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Many ways to skin the cat here. My two-step PROC FREQ also gets those numbers if you simplify the second PROC FREQ:

proc freq data=summarized_counts;

tables MOTPComponentDescription;

run;

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.