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

how to get a count of distinct values from a variable without using proc sql ?

 

example dataset:

 

  ID     Name

101     AAA

102     BBB

103     CCC

105     EEE

101     AAA

103     CCC

1 ACCEPTED SOLUTION

Accepted Solutions
KachiM
Rhodochrosite | Level 12

Which variable to be used to find the distinct observations - either ID or Name can be used in your example. The simplest way is to use Proc sort with nodupkey.

 

For unique Name:

 

proc sort data = have nodupkey;
by Name;
run;

 

For unique ID:

 

proc sort data = have nodupkey;
by id;
run;

 

 There several ways to do with data step and hash.

 

Editor's note: @Tom suggests PROC FREQ with NLEVELS option.  Here's a full example of how that could work.

 

data t;
infile datalines dsd delimiter=',';
input  id $ name $;
datalines;
101,AAA
102,BBB
103,CCC
105,EEE
101,AAA
103,CCC
104,CCC
;
run;

proc freq data=t nlevels noprint;

/* save values of unique ID */
tables id / 
   out=uniqueId (where=(count=1));

/* save values of unique Name */
tables name / 
   out=uniqueName (where=(count=1));

/* save values of unique combination */
tables id * name / 
   out=uniqueWhole (where=(count=1));
run;

 

View solution in original post

4 REPLIES 4
KachiM
Rhodochrosite | Level 12

Which variable to be used to find the distinct observations - either ID or Name can be used in your example. The simplest way is to use Proc sort with nodupkey.

 

For unique Name:

 

proc sort data = have nodupkey;
by Name;
run;

 

For unique ID:

 

proc sort data = have nodupkey;
by id;
run;

 

 There several ways to do with data step and hash.

 

Editor's note: @Tom suggests PROC FREQ with NLEVELS option.  Here's a full example of how that could work.

 

data t;
infile datalines dsd delimiter=',';
input  id $ name $;
datalines;
101,AAA
102,BBB
103,CCC
105,EEE
101,AAA
103,CCC
104,CCC
;
run;

proc freq data=t nlevels noprint;

/* save values of unique ID */
tables id / 
   out=uniqueId (where=(count=1));

/* save values of unique Name */
tables name / 
   out=uniqueName (where=(count=1));

/* save values of unique combination */
tables id * name / 
   out=uniqueWhole (where=(count=1));
run;

 

new510
Fluorite | Level 6

thank you

Tom
Super User Tom
Super User

PROC FREQ NLEVELS

new510
Fluorite | Level 6

Thank you Tom.

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
  • 4 replies
  • 101094 views
  • 5 likes
  • 3 in conversation