topic Re: creating a new variable based on a variable in another data set in SAS Programming

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

ajb — Wed, 23 Oct 2019 04:03:02 GMT

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!

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

KachiM — Wed, 23 Oct 2019 04:57:45 GMT

@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;

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

ChrisNZ — Thu, 24 Oct 2019 00:05:05 GMT

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.

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

ajb — Wed, 23 Oct 2019 23:46:38 GMT

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.

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

ajb — Wed, 23 Oct 2019 23:47:33 GMT

Yes it does. Thank you!