BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
swar
Obsidian | Level 7

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_60dPatient 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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

14 REPLIES 14
PGStats
Opal | Level 21

Please provide meaningful examples of the two tables.

PG
swar
Obsidian | Level 7

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_60dPatient died in 60 days

flg_x_30d

Either flg_30d or flg_rf are set to 1

Reeza
Super User

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. 

swar
Obsidian | Level 7

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

 

PGStats
Opal | Level 21

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;
PG
swar
Obsidian | Level 7

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....

 

PGStats
Opal | Level 21

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.

PG
swar
Obsidian | Level 7
Thanks. But How to set 0 and 1 values to these?
Reeza
Super User

@swar wrote:
Thanks. But How to set 0 and 1 values to these?

Run the code and check the output. 

PGStats
Opal | Level 21

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. 

PG
swar
Obsidian | Level 7

I have another doubt.. Can I write flag variables using proc sql code? If yes how?

 

Thanks a lot for being patient.

 

Reeza
Super User

@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;
swar
Obsidian | Level 7

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..

 

 

PGStats
Opal | Level 21

Check the Log window where SAS tells you why it "is not working".

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 4322 views
  • 5 likes
  • 3 in conversation