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

Hi All,

Hope all is well.

Sorry to bother you but have a quick question/need some advice.  I have a dataset where I want to calculate the distinct counts for a particular variable - see below:

Variable 1 = UNSUBID           Variable 2 = KITNUM

001001                                   4689

001001                                   4689

001001                                   4688

001001                                   4689

001002                                   5000

001002                                   5000

001002                                   5000

001002                                   5000

So, want to calculate the number of distinct counts by UNSUBID for variable KITNUM - so above, 001001 could have a distinct count of 2 for KITNUM (4689 and 4688), 001002 would have a distinct count of 1 for KITNUM (5000) etc.

I have looked on the forums and on SASHELP but cannot quite figure out how to do this (assume it's the count distinct that is required).

Again, thanks for looking and hope you can help 🙂                                 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

proc sql;

     create table WANT as

     select     distinct

                    UNSUBJID,

                    COUNT(DISTINCT KITNUM) as CNT

     from        HAVE

     group by UNSUBJID;

quit;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

proc sql;

     create table WANT as

     select     distinct

                    UNSUBJID,

                    COUNT(DISTINCT KITNUM) as CNT

     from        HAVE

     group by UNSUBJID;

quit;

stat_sas
Ammonite | Level 13

data have;
input UNSUBID  KITNUM;
datalines;
001001 4689
001001 4689
001001 4688
001001 4689
001002 5000
001002 5000
001002 5000
001002 5000
;

proc sql;
select unsubid,count(distinct kitnum) as cnt from have
group by unsubid;
quit;

Brickinnit
Fluorite | Level 6

Thanks All!  Hopefully won't have to ask another dumb question in a while Smiley Happy

Have a good day!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 2709 views
  • 4 likes
  • 3 in conversation