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

Solved
Frequent Contributor
Posts: 79

# 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?

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.

All Replies
Super User
Posts: 2,512

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

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?

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?

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
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?

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?

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?

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?

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.