Hi. I have to create five dummy variables by combining 2 existing tables and assign flags 1 and 0 based on the following conditions.
Column | Set to 1 if and only if: |
flg_If | Diagnosed by Infection |
flg_rf | Diagnosed by respiratory infection |
flg_30d | Patient died within 30 days of discharge(I have dates in another table admission_dt and discharge_dt) |
flg_60d | Patient died in 60 days |
flg_x_30d | Either flg_30d or flg_rf are set to 1 |
Please help... How to create new columns and assign flags at the same time?
Something like this?
data have;
input patientid claim_id
(date_of_admission date_of_discharge death_date) (:mmddyy10.) code $;
format date_of_admission date_of_discharge death_date yymmdd10.;
datalines;
1 1 12/1/2011 1/2/2012 1/31/2012 P2
2 2 12/1/2010 2/2/2012 2/4/2012 R3
;
data want;
set have;
flg_rf = code in ("R3");
flg_lf = code in ("P2", "R3");
flg_30d = intck("day", date_of_discharge, death_date) <= 30;
flg_60d = intck("day", date_of_discharge, death_date) <= 60;
flg_x_30d = flg_30d or flg_rf;
run;
Please provide meaningful examples of the two tables.
The tables are as follows
patientid claim_id date_of_admission date_of_discharge death_date code
1 1 12/1/2011 1/2/2012 1/31/2012 P2
2 2 12/1/2010 2/2/2012 2/4/2012 R3
Second table
Code Description
P2 Infection
R3 Respiratory infection
Now I should create a combined table containing the following columns
patient_id claim_id and the below five dummy variables by combining 2 existing tables and assign flags 1 and 0 based on the following conditions.
Column | Set to 1 if and only if: |
flg_If | Diagnosed by Infection |
flg_rf | Diagnosed by respiratory infection |
flg_30d | Patient died within 30 days of discharge(I have dates in another table admission_dt and discharge_dt) |
flg_60d | Patient died in 60 days |
flg_x_30d | Either flg_30d or flg_rf are set to 1 |
Your sample data should include at minimum 1 of each case of flag, bonus if you have all 2 way, 3 way and 4 way flags and cases where no flags are set.
This will allow you to test your logic thoroughly.
I have given a sample table.
So, how do I create those 5 new columns as assign flag variables to them using proc sql?
Please help
Something like this?
data have;
input patientid claim_id
(date_of_admission date_of_discharge death_date) (:mmddyy10.) code $;
format date_of_admission date_of_discharge death_date yymmdd10.;
datalines;
1 1 12/1/2011 1/2/2012 1/31/2012 P2
2 2 12/1/2010 2/2/2012 2/4/2012 R3
;
data want;
set have;
flg_rf = code in ("R3");
flg_lf = code in ("P2", "R3");
flg_30d = intck("day", date_of_discharge, death_date) <= 30;
flg_60d = intck("day", date_of_discharge, death_date) <= 60;
flg_x_30d = flg_30d or flg_rf;
run;
Thank you! But there are so many errors in flag creation.
My date columns have null values ie missing values in the table..
1.When I use this command with proc sql,
intck('day', dt_discharge,dt_death) le 30 as flg_30d ,
when dt_death has missing values it should give 0 as output but it is returning 1.
2. Also, flg_30d or flg_rf as flg_x_30d cannot be used as such in proc sql right?
I suppose I should use the entire expressions intck('day', dt_discharge,dt_death) le 30 or intck('day', date_of_discharge, death_date) le 60 as flg_60d as flg_x_30d in proc sql to flag those variables.
3. There is another expression in intck('day', dt_discharge,dt_admission) where I have to find the difference between discharge date and second successive admission date to hospital. So how would I find second instance of dt_admission ?
kindly help....
1) Replace the missing death date with a much later date
intck('day', dt_discharge,coalesce(dt_death,"31dec2100"d)) le 30 as flg_30d ,
2) Refer to previously calculated values with the keyword calculated
calculated flg_30d or calculated flg_rf as flg_x_30d
3) More detail needed. Where is that re-admission date in your data? I suggest that you post as a new question.
@swar wrote:
Thanks. But How to set 0 and 1 values to these?
Run the code and check the output.
SAS sets the value to 0 for FALSE and 1 for TRUE. So
flg_rf = code in ("R3");
will assign the value 1 to flg_rf if code is "R3" and 0 otherwise.
I have another doubt.. Can I write flag variables using proc sql code? If yes how?
Thanks a lot for being patient.
@swar wrote:
I have another doubt.. Can I write flag variables using proc sql code? If yes how?
Thanks a lot for being patient.
That's not a doubt, it's a question.
Yes you can, the exact same way in fact but using the appropriate SQL statements.
proc sql;
create table want as
select *, sex='F' as indicator_sex
from sashelp.class;
quit;
This code is not working.
I have used it like this
proc sql;
create table Table3 as
select *, condition= 'R3' as flg_rf
from table2;
select * from Table3;
I have to create 5 dummy variables..
Check the Log window where SAS tells you why it "is not working".
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.