DATA Step, Macro, Functions and more

How to obtain count on this table?

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

How to obtain count on this table?

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

 

 


Accepted Solutions
Solution
‎01-06-2017 03:33 AM
Valued Guide
Posts: 797

Re: How to obtain count on this table?

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

Re: How to obtain count on this table?

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;
Thanks,
Jag
Super User
Posts: 17,861

Re: How to obtain count on this table?

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. 

Super Contributor
Posts: 305

Re: How to obtain count on this table?

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;
Super User
Posts: 17,861

Re: How to obtain count on this table?

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. 

Solution
‎01-06-2017 03:33 AM
Valued Guide
Posts: 797

Re: How to obtain count on this table?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 208 views
  • 1 like
  • 5 in conversation