Help using Base SAS procedures

creating dummy variables and assigning flags

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

creating dummy variables and assigning flags

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?

 


Accepted Solutions
Solution
‎04-21-2017 11:37 PM
Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

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


All Replies
Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

Please provide meaningful examples of the two tables.

PG
Contributor
Posts: 22

Re: creating dummy variables and assigning flags

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

Super User
Posts: 19,789

Re: creating dummy variables and assigning flags

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. 

Contributor
Posts: 22

Re: creating dummy variables and assigning flags

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

 

Solution
‎04-21-2017 11:37 PM
Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

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
Contributor
Posts: 22

Re: creating dummy variables and assigning flags

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

 

Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

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
Contributor
Posts: 22

Re: creating dummy variables and assigning flags

Thanks. But How to set 0 and 1 values to these?
Super User
Posts: 19,789

Re: creating dummy variables and assigning flags


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

Run the code and check the output. 

Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

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
Contributor
Posts: 22

Re: creating dummy variables and assigning flags

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

 

Thanks a lot for being patient.

 

Super User
Posts: 19,789

Re: creating dummy variables and assigning flags


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;
Contributor
Posts: 22

Re: creating dummy variables and assigning flags

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

 

 

Respected Advisor
Posts: 4,920

Re: creating dummy variables and assigning flags

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

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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