topic Re: How would I do this calculation in SAS? in SAS Procedures

How would I do this calculation in SAS?

ajb — Sat, 13 Sep 2014 03:00:55 GMT

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!

Re: How would I do this calculation in SAS?

art297 — Sat, 13 Sep 2014 03:47:15 GMT

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;

Re: How would I do this calculation in SAS?

Reeza — Sat, 13 Sep 2014 03:47:49 GMT

What would you want your output to look like? Why 2 variables for a similar calculation?

Why Kevin's Var 1 value?

Re: How would I do this calculation in SAS?

stat_sas — Sat, 13 Sep 2014 04:23:47 GMT

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;

Re: How would I do this calculation in SAS?

Tom — Sat, 13 Sep 2014 23:24:21 GMT

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

;;;;