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
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.
Like this?
proc sql;
select day, y, count(distinct ID) from EQ group by 1,2;
quit;
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?
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.
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?
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 |
Thanks a lot .
Could I also use this method for unknown ID number with unknown variable number?
What does this mean?
Please give a precise and complete example as it seems that your request keeps changing.
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?
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.