Hi,
I have data table as shown below:
ID | Group | Sub Group | Flag |
101 | A | AB | 1 |
110 | A | AB | 1 |
103 | A | IJ | 1 |
108 | A | KL | 1 |
501 | B | AB | 2 |
503 | B | CD | 2 |
507 | B | EF | 1 |
508 | B | EF | 1 |
509 | B | EF | 1 |
515 | B | KL | 2 |
511 | B | KL | 2 |
I need to count unique ID by Flag as shown below:
Flag-1 | ||
Group | Sub Group | count of ID |
A | AB | 2 |
A | IJ | 1 |
A | KL | 1 |
Flag-2 | ||
Group | Sub Group | count of ID |
B | AB | 1 |
B | CD | 1 |
B | EF | 3 |
B | KL | 2 |
Thanks,
Ganesh K
The "distinct id" in Fareeza's proc sql keeps it to a single PROC. Otherwise, if you don't use a data step, then you probably need two proc's. Recursive proc freqs?
proc freq data=have noprint ;
tables flag*group*subgroup*id / out=need;
run;
proc freq data=need noprint;
table flag*group*subgroup / out=want (drop=percent rename=(count=n_ids));
run;
x
data have;
input ID Group$ Sub_Group$ Flag;
cards;
101 A AB 1
110 A AB 1
103 A IJ 1
108 A KL 1
501 B AB 2
503 B CD 2
507 B EF 1
508 B EF 1
509 B EF 1
515 B KL 2
511 B KL 2
;
proc sort data=have;
by group sub_group flag;
run;
data flag1 flag2;
set have;
by group sub_group flag;
retain count;
if first.flag then count=1;
else count=count+1;
drop id;
if last.flag;
if flag=1 then output flag1;
else if flag=2 then output flag2;
run;
When counting unique records, SQL is helpful.
Proc SQL;
create table want as
select flag, group, subgroup, count(distinct ID) as uniqueiDs
from have
group by flag, group, subgroup;
quit.
If you want the strucutre shown shown in your post, use a proc report or proc PRINT on the table from the query above.
Hello,
data have;
infile datalines;
input ID Group $ Sub_Group $ Flag;
datalines;
101 A AB 1
110 A AB 1
103 A IJ 1
108 A KL 1
501 B AB 2
503 B CD 2
507 B EF 1
508 B EF 1
509 B EF 1
515 B KL 2
511 B KL 2
;
proc means noprint nway data=have;
class Group Sub_Group;
var flag;
output out=want(drop=_type_ _freq_) N(Flag)=;
run;
Proc means cannot do a distinct count. If your actual data is like your sample data and you have no duplicate rows/repeat customers per flag/group/subgroup this will be fine.
The "distinct id" in Fareeza's proc sql keeps it to a single PROC. Otherwise, if you don't use a data step, then you probably need two proc's. Recursive proc freqs?
proc freq data=have noprint ;
tables flag*group*subgroup*id / out=need;
run;
proc freq data=need noprint;
table flag*group*subgroup / out=want (drop=percent rename=(count=n_ids));
run;
x
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.