obs | Name | var 1 | var 2 | var 3 |
1 | lara | 12 | 13 | 12 |
2 | paul | 23 | 87 | 32 |
3 | steve | 45 | 99 | 33 |
4 | kevin | 67 | 87 | 56 |
5 | peter | 86 | 56 | 86 |
6 | john | 12 | 44 | 45 |
Hi,
What I want to do is fairly simple (I believe) if you know what to do. I want to write SAS code that goes something like this:
newvar1=(Paul's var1 value) / (Kevin's var1 value)
newvar2=(Steve's var1 value) /( Kevin's var1 value)
I am trying to find a way to do this that is not the brute force method but I can't think of a way to do this. Is there some sophisticated way of doing this??
I am not at all familiar with macros so if the answer includes macros you may need to walk me through it a bit.
Thanks!
K
Not sure what you really want to do, but the following does what you asked:
data have;
input Name $ var1-var3;
cards;
lara 12 13 12
paul 23 87 32
steve 45 99 33
kevin 67 87 56
peter 86 56 86
john 12 44 45
;
proc transpose data=have (rename=(name=_name_)) out=want (drop=_:);
var var1;
run;
data want (drop=lara--john);
set want;
newvar1=paul/kevin;
newvar2=steve/kevin;
run;
What would you want your output to look like? Why 2 variables for a similar calculation?
Why Kevin's Var 1 value?
As Art has already suggested a solution. Without having desired output it's hard to recommed a solution. If you are familiar with proc sql, try something like this
proc sql;
select *,case when Name='paul' then var1/(select var1 from have where
Name='kevin') end as newvar1,
case when Name='steve' then var1/(select var1 from have where
Name='kevin') end as newvar2
from have;
quit;
What do you want the output to look like? Why KEVIN? Do you want all combinations?
Here is a way to get all combinations. But I have limited to half by only keeping when NAME1 > NAME2.
data have ;
input name $ var1-var3 ;
cards;
paul 23 87 32
steve 45 99 33
kevin 67 87 56
;;;;
proc transpose data=have out=vertical ;
by name notsorted;
var var1-var3 ;
run;
proc sql noprint ;
create table want as
select a.name as name1
, b.name as name2
, a._name_ as variable
, a.col1 as value1
, b.col1 as value2
, a.col1 / b.col1 as ratio
from vertical a
, vertical b
where a.name > b.name
and a._name_ = b._name_
order by 1,2,3
;
quit;
Here is the resulting WANT dataset.
data want ;
input (name1 name2 variable) ($) value1 value2 ratio;
cards;
paul kevin var1 23 67 0.3432835821
paul kevin var2 87 87 1
paul kevin var3 32 56 0.5714285714
steve kevin var1 45 67 0.671641791
steve kevin var2 99 87 1.1379310345
steve kevin var3 33 56 0.5892857143
steve paul var1 45 23 1.9565217391
steve paul var2 99 87 1.1379310345
steve paul var3 33 32 1.03125
;;;;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.