topic Re: How to obtain count on this table? in SAS Programming

How to obtain count on this table?

Ganeshk — Thu, 29 Dec 2016 03:54:44 GMT

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

Re: How to obtain count on this table?

Jagadishkatam — Thu, 29 Dec 2016 04:01:28 GMT

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;

Re: How to obtain count on this table?

Reeza — Thu, 29 Dec 2016 05:45:27 GMT

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.

Re: How to obtain count on this table?

Loko — Thu, 29 Dec 2016 08:44:28 GMT

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;

Re: How to obtain count on this table?

Reeza — Thu, 29 Dec 2016 10:05:40 GMT

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.

Re: How to obtain count on this table?

mkeintz — Thu, 29 Dec 2016 14:01:03 GMT

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;