how to count the frequency of each distinct y each day?

[xiangpang]

Hello, 

I want to count the number of ID with distinct y in each day. Make a table like 'want'. Could anyone tell me how to do that? Thanks a lot!!

data eq;
input ID day y ;
cards;
1 1 a 
1 2 b 
1 3 c 
1 4 v 
2 1 a 
2 2 b 
2 3 cc 
2 4 v 
3 1 a 
3 2 bb 
3 3 c 
3 4 v  
4 1 a 
4 2 bb   
4 3 ccc 
4 4 vv 
5 1 a 
5 2 b 
5 3 ccc 
5 4 vvv
run;

want
day1    a 5
	aa 0
	aaa 0
day2    b 3
	bb 2
	bbb 0
day3    c 2
	cc 1
	ccc 2
day4    v 1
	vv 1
	vvv 3

[ChrisNZ]

Like this?

proc sql;
  select day, y, count(distinct ID) from EQ group by 1,2;
quit;

[xiangpang]

Actually, this is same result as proc freq. But it is not exactly what I want, since it does not include the missing ones, such as the frequency of aa or aaa. I think I may have to add it manually. That is why I asked how to add a row in another post. Do you have a better idea?

[ChrisNZ]

There's no aa in your data, how you expect that in the output?

You 'll need a reference table with all the values.

Then you can use this table in a join if you go the SQL route, or with PROC MEANS CLASSDATA= if you prefer a procedure. 

[xiangpang]

Thanks for your input. Do you mind to explain a little bit more or give a link? how to make a reference table? I prefer a procedure. how to use classdata= to do this job? 

[ChrisNZ]

I am sure you can find the link.  🙂   This works:

data EQ;
input ID DAY Y $;
cards;
1 1 a 
1 2 b 
1 3 c 
1 4 v 
2 1 a 
2 2 b 
2 3 cc 
2 4 v 
3 1 a 
3 2 bb 
3 3 c 
3 4 v  
4 1 a 
4 2 bb   
4 3 ccc 
4 4 vv 
5 1 a 
5 2 b 
5 3 ccc 
5 4 vvv
run;
data CLASSDATA;
  input DAY Y $;
cards;
1 a 
1 aa 
1 aaa 
2 b 
2 bb 
2 bb 
3 c 
3 cc 
3 ccc
4 v
4 vv
4 vvv
run;
proc summary data=EQ(drop=ID) classdata=CLASSDATA nway n;
  class DAY Y;
  output out=WANT ( drop=_TYPE_);
run;

DAY	Y	_FREQ_
1	a	5
1	aa	0
1	aaa	0
2	b	3
2	bb	2
3	c	2
3	cc	1
3	ccc	2
4	v	3
4	vv	1
4	vvv	1

[xiangpang]

Thanks a lot . 

[xiangpang]

Could I also use this method for unknown ID number with unknown variable number? 

[ChrisNZ]

What does this mean?

Please give a precise and complete example as it seems that your request keeps changing.

[xiangpang]

Sorry for the confusion. Maybe it should be another question. The example only have 5 IDs with 4 different date and 4 different value for Y. However, in real case, when I have thousands of IDs with a hundred different Y, what is the easiest way to create the classdata? 

[ChrisNZ]

 what is the easiest way to create the classdata? 

I don't know.  

Who decides that day 1 only has Y= a, aa, and aaa?

Where are these chosen combinations coming from?

How do you tell the program? 

[AndrewHowell · Accepted solution]

BTW - it needs to be:

input ID day y $;

And shouldn't Day 4 be

day4    v 3
	vv 1
	vvv 1

How do you expect to derive values which aren't in the source data: aa, aaa, bbb?

That aside, most of what you want can be achieved with PROC FREQ options, such as:

PROC FREQ DATA = eq;
	TABLES day * y / NOROW NOCOL NOPERCENT NOCUM LIST;
	TABLES day * y / NOROW NOCOL NOPERCENT NOCUM CROSSLIST;
	TABLES day * y / NOROW NOCOL NOPERCENT NOCUM OUT=MyOutputTable;
RUN;
proc print data=MyOutputTable(drop=PERCENT) noobs;
run;

Check out PROC FREQ in the SAS Online Documentation.

[ballardw]

By table do you mean a data set to manipulate further or a table for people to read. If the desire is something for people perhaps:

proc tabulate data=eq;
  class day y;
  tables day*y,
         n
   ;
run;