DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 79
Accepted Solution

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

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

  


Accepted Solutions
Solution
3 weeks ago
Moderator
Posts: 329

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

Posted in reply to xiangpang

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.

 

View solution in original post


All Replies
Super User
Posts: 2,512

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

Posted in reply to xiangpang

 

Like this?

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

 

 

Frequent Contributor
Posts: 79

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

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?

 

 

Super User
Posts: 2,512

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

Posted in reply to xiangpang

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. 

 

Frequent Contributor
Posts: 79

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

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? 

 

 

Super User
Posts: 2,512

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

Posted in reply to xiangpang

I am sure you can find the link.  Smiley Happy   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
Frequent Contributor
Posts: 79

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

Thanks a lot . 

Frequent Contributor
Posts: 79

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

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

Super User
Posts: 2,512

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

Posted in reply to xiangpang

What does this mean?

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

Frequent Contributor
Posts: 79

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

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

Super User
Posts: 2,512

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

Posted in reply to xiangpang

 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? 

Solution
3 weeks ago
Moderator
Posts: 329

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

Posted in reply to xiangpang

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.

 

Super User
Posts: 13,941

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

Posted in reply to xiangpang

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;
☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 126 views
  • 3 likes
  • 4 in conversation