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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.