topic Re: Unique value count by variable in SAS Programming

Unique value count by variable

jay_bhavsar — Wed, 13 Jan 2021 13:25:10 GMT

Data have
Result PID treatment
Yes 1 T
No 1 T
Yes 1 T
Yes 1 O
No 2 R
Yes 2 R
Yes 2 O
Yes 3 T
Yes 3 T
Yes 3 T
Yes 3 O
Result want count
T R O
2 1 3

Thanka in advance

Re: Unique value count by variable

PhilC — Wed, 13 Jan 2021 13:31:02 GMT

WIll this help:

Your data ain't good looking, but it has a nice cardinality - The SAS Dummy

@ChrisHemedinger, I still laugh at the song lyrics in this article.

Re: Unique value count by variable

PeterClemmensen — Wed, 13 Jan 2021 13:50:05 GMT

There you go.

Transpose if you want

data have;
input Result $ PID treatment $;
datalines;
Yes 1 T
No 1 T
Yes 1 T
Yes 1 O
No 2 R
Yes 2 R
Yes 2 O
Yes 3 T
Yes 3 T
Yes 3 T
Yes 3 O
;

proc sql;
   create table want as
   select treatment,
          count(distinct PID) as count
   from have
   group by treatment;
quit;

Re: Unique value count by variable

ballardw — Wed, 13 Jan 2021 15:53:46 GMT

Do you want a data set for manipulation or a report people read?

If you don't mind a vertical report the Nlevels option in Proc Freq may suit.

Example:

proc freq data=sashelp.class nlevels;
   ods output nlevels=work.levels;
   ods select nlevels;
run;

Which if you have MISSING values will also have a summary involving those n the work.nlevels data set created. If you don't like the vertical layout you could transpose the work.levels, or write a report to use the work.levels.

Re: Unique value count by variable

jay_bhavsar — Sat, 16 Jan 2021 04:33:33 GMT

Dear PeterClemmensen,
Thanks for your answer. your SQL giving me the result, but one more thing i want to add the i only want to consider yes from result variable. so i added onw more patient. Then how to calculate the count?
data have;
input Result $ PID treatment $;
datalines;
Yes 1 T
No 1 T
Yes 1 T
Yes 1 O
No 2 R
Yes 2 R
Yes 2 O
Yes 3 T
Yes 3 T
Yes 3 T
Yes 3 O
No 4 R
No 4 R
;

Re: Unique value count by variable

PeterClemmensen — Sat, 16 Jan 2021 06:00:45 GMT

Try this

proc sql;
   create table want as
   select treatment,
          count(distinct PID) as count
   from have
   where Result = 'Yes'
   group by treatment;
quit;

Re: Unique value count by variable

jay_bhavsar — Sat, 16 Jan 2021 11:13:44 GMT

It worked...
I learned new thing today. Thank you so much for your support.