BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xiangpang
Quartz | Level 8

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

  

1 ACCEPTED SOLUTION

Accepted Solutions
AndrewHowell
Moderator

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

12 REPLIES 12
ChrisNZ
Tourmaline | Level 20

 

Like this?

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

 

 

xiangpang
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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

ChrisNZ
Tourmaline | Level 20

What does this mean?

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

xiangpang
Quartz | Level 8

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
Tourmaline | Level 20

 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
Moderator

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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