So I have a variable called cumlative dosage. I want to code a patient to see if they are above or the below the median dosage.
So when I run:
proc means data=temp n min max median;
var avcumdos_6wper;
run;
This is the following output
N Minimum Maximum Median
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
184 1.1695906 10.4000000 4.0354701
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
So I want to calculate a flag variable around that mean. where flag=. if avcumdos_6wper=., flag=1 if avcumdos_6wper <= median or flag=2 if avcumdos_6wper> median.
So I figure I can do this via sql, but i can't seem to get it to work. It just copies the variable. This is my code thus far:
proc sql;
create table temp1 as
select patientid, visit, avcumdos_6wper, median(avcumdos_6wper) as mv,
case when avcumdos_6wper = . then .
when avcumdos_6wper <= calculated mv then 1
when avcumdos_6wper > calculated mv then 2
else 999 end as flag
from temp;
Can someone tell me what I am doing wrong?
Are you on SAS 9.4? Before SAS 9.4 proc SQL cannot calculate the median.
If median works it is only working on one record at a time with your syntax. My current SAS does not have the median function available in SQL but you may be looking for something like:
proc sql;
create table temp1 as
select patientid, visit, avcumdos_6wper, b.mv,
case when avcumdos_6wper = . then .
when avcumdos_6wper <= b.mv then 1
when avcumdos_6wper > b.mv then 2
else 999 end as flag
from temp join (select median(avcumdos_6wper) as mv from temp) as b;
quit;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.