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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.