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

Hello.

I have a problem in calculation ratio for diffierent values in a variable by group.

For example, I have a dataset like this.

 

iddatenamevar1
1161231ax
1161231ay
1161231by
1161231cx
1161231cy
1161231dz
2161231ex
2161231ey
2161231fx
2161231fz
2161231gz

 

I want to calculate (number of a value in var1)/(number of distinct names) by id-date groups.

For example, for id 1 - date 161231 group, I want x_ratio=2/4 y_ratio=3/4 z_ratio=1/4.

Also, for id 2 - date 161231 group, I want x_ratio=2/3 y_ratio=1/3 z_ratio=2/3.

 

If you have any suggestion, please share it with me.

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
JohnHoughton
Quartz | Level 8

One option, plus the code for transpose in case you want the ratios in separate columns

 

proc sql;
create table want as
select a.id,a.date,var1,nvalInVar1 ,nDistinctNames,nvalInVar1/nDistinctNames as Ratio
 from (select id,date,var1,count (*) as nvalInVar1 from have group by id,date,var1) a
join (select id,date,count(distinct(name)) as nDistinctNames from have group by id,date) b
on a.id=b.id and a.date=b.date;

proc transpose data=want out=want2 (drop=_name_) prefix=ratio_; 
by id date; 
id var1 ;
var ratio;
 run; 

 

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

I can't understand how when id=1, z_ratio=1/4 from your data. You need to explain in more detail how these ratios are computed.

--
Paige Miller
Astounding
PROC Star

@PaigeMiller,

 

I don't have time to solve this right now, but I can explain where the numbers come from.

 

The denominator is 4 because there are 4 distinct NAME values for the combination of ID and DATE.

 

The numerator is 1 because there is only 1 observation for the combination of ID, DATE, and VAR1.

JohnHoughton
Quartz | Level 8

One option, plus the code for transpose in case you want the ratios in separate columns

 

proc sql;
create table want as
select a.id,a.date,var1,nvalInVar1 ,nDistinctNames,nvalInVar1/nDistinctNames as Ratio
 from (select id,date,var1,count (*) as nvalInVar1 from have group by id,date,var1) a
join (select id,date,count(distinct(name)) as nDistinctNames from have group by id,date) b
on a.id=b.id and a.date=b.date;

proc transpose data=want out=want2 (drop=_name_) prefix=ratio_; 
by id date; 
id var1 ;
var ratio;
 run; 

 

novinosrin
Tourmaline | Level 20
data have;
input id date	name $	var1 $;
datalines;
1	161231	a	x
1	161231	a	y
1	161231	b	y
1	161231	c	x
1	161231	c	y
1	161231	d	z
2	161231	e	x
2	161231	e	y
2	161231	f	x
2	161231	f	z
2	161231	g	z
;
proc freq data=have  noprint ;
by id date;
tables var1/out=temp(drop=percent) ;
run; 


proc sql;
create table want as
select a.*,b.cnt, a.count/b.cnt as ratio
from 
temp a left join (select id, date,count(distinct name) as cnt from have group by id, date) b
on a.id=b.id and a.date=b.date;
quit;
Sejin
Obsidian | Level 7

Everyone, thanks for sharing your knowledge.

I can't thank you enough. It helped a lot. 🙂

sas-innovate-2024.png

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.

 

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
  • 5 replies
  • 3149 views
  • 3 likes
  • 5 in conversation