BookmarkSubscribeRSS Feed
ajb
Obsidian | Level 7 ajb
Obsidian | Level 7
          obsNamevar 1      var 2    var 3
1lara121312
2paul238732
3steve459933
4kevin678756
5peter865686
6john124445

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

4 REPLIES 4
art297
Opal | Level 21

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;

Reeza
Super User

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

Why Kevin's Var 1 value?

stat_sas
Ammonite | Level 13

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;

Tom
Super User Tom
Super User

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

;;;;

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!

What is Bayesian Analysis?

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.

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
  • 4 replies
  • 760 views
  • 0 likes
  • 5 in conversation