creating a new variable based on a variable in another data set

[ajb]

Hello Everyone,

I have a data set called metabolic and I would like to calculate the median value of a specific variable sysbp. I would then like to create a new variable in metabolic called median, and give all observations the same median value calculated in med data set.  From this I want to calculate the absolute difference between sysbp and median.  I need to automate this as I will eventually need to create a marco.

This is the code that I have been playing with in SAS 9.4:

proc univariate data=metabolic noprint;
var sysbp;
output out=med
mean=mean
median=median
run;

data new;
merge metabolic
med(keep=median);
diff = abs(sysbp - median);
run;

Med data set  

  mean  median

1 130   134

Metabolic data set

    ID  SYSBP

1   11   135

2   12   102

3   13   145

...   

n   nn   101

Wanted: New data set

    ID  SYSBP Median

1   11   135    134

2   12   102    134

3   13   145    134

...   

n   nn   101    134

Incorrect data set that I keep getting 

    ID  SYSBP Median

1   11   135    134

2   12   102    .

3   13   145    .

...   

n   nn   101    .

I have also tried this code and it did not give me the right data set either

data new;
merge metabolic
med(keep=median);

if first.habcid then median2=median;
else median2=first.median;
diff = abs(sysbp - median2);
run;

I am very rusty with SAS so any help in tweeking the code is greatly appreciated!

[KachiM · Accepted solution]

@ajb 

You need one value (MEDIAN) from MED data set, it can be done as:

data new;
   if _n_ = 1 then set med(keep=median);
   set metabolic;
   diff = abs(sysbp - median);
run;

[ajb]

Thank you! I have never seen two set statements used in this way. I had to read up on it. I have learnt something new.

[ChrisNZ]

The way you do it, SAS tries to find the second observation of the median table.

With @KachiM's code, SAS only reads the first observation of the median table, and because it doesn't try to access it again, the PDV is never reinitialised for the median variable.

I hope this makes sense.

[ajb]

Yes it does. Thank you!