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".
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.