## How would I do this calculation in SAS?

Occasional Contributor
Posts: 18

# How would I do this calculation in SAS?

 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

PROC Star
Posts: 8,165

## Re: How would I do this calculation in SAS?

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;

Super User
Posts: 23,771

## Re: How would I do this calculation in SAS?

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

Why Kevin's Var 1 value?

Posts: 1,270

## Re: How would I do this calculation in SAS?

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;

Super User
Posts: 8,120

## Re: How would I do this calculation in SAS?

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

;;;;

Discussion stats
• 4 replies
• 243 views
• 0 likes
• 5 in conversation