About lizzy28

lizzy28 · ‎08-03-2022

Thank you all! @FreelanceReinh provided the right way to correct it.

lizzy28 · ‎08-03-2022

I accepted a response as a solution for my question by mistake. Is there a way to revoke it and accept another response as the solution? Thank you!

lizzy28 · ‎08-02-2022

Thanks again, Kurt! This works very well! Could you explain a bit about the code below? I have difficulty in understanding the part conceptually. if not first.provider_id and _dis ne . and admission_dt gt sum(_dis,1) then do; _tmp = admission_dt; admission_dt = _start; _start = _tmp; _tmp = discharge_dt; discharge_dt = _dis; length_of_stay = discharge_dt - admission_dt; claim_id_discharge = _claim_id; output; discharge_dt = _tmp; claim_id_adm = claim_id;

lizzy28 · ‎08-01-2022

I previously asked how to construct inpatient stays and length of stay here: https://communities.sas.com/t5/SAS-Programming/Count-number-of-inpatient-stays-and-length-of-stay/m-p/826509#M326470. On top of that, I'd like to obtain a bit more admission and discharge information per stay. Say, each encounter has a individual claim_id. For each inpatient stay, I wanted to keep the admission claim_id (claim_id when a person was admitted) and the discharge claim_id (claim_id when a person was discharged). The example data look like below (only column claim_id is added, the rest is the same as the old post). PERSON_ID provider_id admission_dt discharge_dt claim_id Note 1 A 8/1/2014 1/7/2018 12345 a stay starts (admission_dt) from previous years, 1/1/2018 will be used as initial admission_dt 1 A 8/1/2014 1/10/2018 12346 Overlapped stay, so it's considered as the same stay as the stay in last claim (last observation) 1 A 1/11/2018 1/31/2018 12347 Consecutive date (date difference<=1) from last observation, so the same inpatient stay 1 A 1/11/2018 2/28/2018 12348 1 A 1/11/2018 2/28/2018 12349 1 A 1/11/2018 3/31/2018 12350 1 A 1/11/2018 4/30/2018 12351 1 A 5/16/2018 5/31/2018 12352 Non-consecutive date from last admission, so a separate inpatient stay 1 A 5/16/2018 6/30/2018 12353 1 A 5/16/2018 7/31/2018 12354 1 A 5/16/2018 8/31/2018 12355 1 A 5/16/2018 9/30/2018 12356 1 A 5/16/2018 10/31/2018 12357 1 A 5/16/2018 11/30/2018 12358 1 A 5/16/2018 12/31/2018 12358 2 B 11/1/2017 1/31/2018 21234 2 B 12/24/2017 1/1/2018 21235 2 B 11/1/2017 2/28/2018 21236 2 B 11/1/2017 3/31/2018 21237 2 B 11/1/2017 4/30/2018 21238 2 B 11/1/2017 5/31/2018 21239 2 B 11/1/2017 6/30/2018 21240 2 B 11/1/2017 7/31/2018 21241 2 B 11/1/2017 8/31/2018 21242 2 B 11/1/2017 9/30/2018 21243 2 B 11/1/2017 10/5/2018 21244 2 C 10/6/2018 10/31/2018 21245 Consecutive date from last admission but with different provider, so a separate inpatient stay 2 C 10/5/2018 11/30/2018 21246 2 C 10/5/2018 12/31/2018 21247 3 D 1/27/2018 1/30/2018 31234 3 D 1/27/2018 2/28/2018 31235 3 E 3/15/2018 31236 A new inpatient stay at a different provider 15 days after the last stay 3 E 3/15/2018 31237 Missing discharge date means the person is not discharged per claim, so 12/31/2018 will be used as the discharge date for the stay 3 E 3/15/2018 31238 3 E 3/15/2018 31239 3 E 3/15/2018 31240 3 E 3/15/2018 31241 3 E 4/1/2018 31242 3 E 5/1/2018 31243 3 E 6/1/2018 31244 3 E 7/1/2018 31245 3 E 8/1/2018 31246 3 E 9/1/2018 31247 3 E 10/1/2018 31248 3 E 11/1/2018 31249 3 E 12/1/2018 31250 The target output would be PERSON_ID provider_id admission_dt discharge_dt length_of_stay claim_id_adm claim_id_discharge 1 A 8/1/2014 4/30/2018 1368 12345 12351 1 A 5/16/2018 12/31/2018 229 12352 12358 2 B 11/1/2017 10/5/2018 338 21234 21244 2 C 10/5/2018 12/31/2018 87 21246 21247 3 D 1/27/2018 2/28/2018 32 31234 31235 3 E 3/15/2018 12/31/2018 291 31236 31250 where the last two columns are newly targeted column compared to the old post. Much appreciated!

lizzy28 · ‎08-01-2022

Hi Kurt, Thank you for the solution! The code works well. However, I have trouble in understanding the part as below if not first.provider_id and _dis ne . and admission_dt gt sum(_dis,1) then do; _tmp = admission_dt; admission_dt = _start; _start = _tmp; _tmp = discharge_dt; discharge_dt = _dis; length_of_stay = discharge_dt - admission_dt; output; discharge_dt = _tmp; end; I actually tried to add a bit more code to include claim id info. That is, if there is another column called claim_id, I wanted to keep admission claim_id and discharge claim_id per stay. The example data would be PERSON_ID provider_id admission_dt discharge_dt claim_id Note 1 A 8/1/2014 1/7/2018 12345 a stay starts (admission_dt) from previous years, 1/1/2018 will be used as initial admission_dt 1 A 8/1/2014 1/10/2018 12346 Overlapped stay, so it's considered as the same stay as the stay in last claim (last observation) 1 A 1/11/2018 1/31/2018 12347 Consecutive date (date difference<=1) from last observation, so the same inpatient stay 1 A 1/11/2018 2/28/2018 12348 1 A 1/11/2018 2/28/2018 12349 1 A 1/11/2018 3/31/2018 12350 1 A 1/11/2018 4/30/2018 12351 1 A 5/16/2018 5/31/2018 12352 Non-consecutive date from last admission, so a separate inpatient stay 1 A 5/16/2018 6/30/2018 12353 1 A 5/16/2018 7/31/2018 12354 1 A 5/16/2018 8/31/2018 12355 1 A 5/16/2018 9/30/2018 12356 1 A 5/16/2018 10/31/2018 12357 1 A 5/16/2018 11/30/2018 12358 1 A 5/16/2018 12/31/2018 12358 2 B 11/1/2017 1/31/2018 21234 2 B 12/24/2017 1/1/2018 21235 2 B 11/1/2017 2/28/2018 21236 2 B 11/1/2017 3/31/2018 21237 2 B 11/1/2017 4/30/2018 21238 2 B 11/1/2017 5/31/2018 21239 2 B 11/1/2017 6/30/2018 21240 2 B 11/1/2017 7/31/2018 21241 2 B 11/1/2017 8/31/2018 21242 2 B 11/1/2017 9/30/2018 21243 2 B 11/1/2017 10/5/2018 21244 2 C 10/6/2018 10/31/2018 21245 Consecutive date from last admission but with different provider, so a separate inpatient stay 2 C 10/5/2018 11/30/2018 21246 2 C 10/5/2018 12/31/2018 21247 3 D 1/27/2018 1/30/2018 31234 3 D 1/27/2018 2/28/2018 31235 3 E 3/15/2018 31236 A new inpatient stay at a different provider 15 days after the last stay 3 E 3/15/2018 31237 Missing discharge date means the person is not discharged per claim, so 12/31/2018 will be used as the discharge date for the stay 3 E 3/15/2018 31238 3 E 3/15/2018 31239 3 E 3/15/2018 31240 3 E 3/15/2018 31241 3 E 4/1/2018 31242 3 E 5/1/2018 31243 3 E 6/1/2018 31244 3 E 7/1/2018 31245 3 E 8/1/2018 31246 3 E 9/1/2018 31247 3 E 10/1/2018 31248 3 E 11/1/2018 31249 3 E 12/1/2018 31250 The target data would be PERSON_ID provider_id admission_dt discharge_dt claim_id_adm claim_id_discharge length_of_stay 1 A 8/1/2014 4/30/2018 12345 12351 1368 1 A 5/16/2018 12/31/2018 12352 12358 229 2 B 11/1/2017 10/5/2018 21234 21244 338 2 C 10/5/2018 12/31/2018 21246 21247 87 3 D 1/27/2018 2/28/2018 31234 31235 32 3 E 3/15/2018 12/31/2018 31236 31250 291 Any further guidance is greatly appreciated!

lizzy28 · ‎07-07-2022

Thank you, Patrick! I forgot to mention that missing discharge date means one has not been discharged. So this case the rows are technically all overlapped per individual.

lizzy28 · ‎07-07-2022

Hi all, I have multiple rows of admission dates (admission_dt) and discharge dates (discharge_dt) per person (identified by DummyID) and wanted to identify if the timeframe (between admission_dt and discharge_dt) across multiple rows have any overlapped days. Any suggestion is greatly appreciated! Below is the sample data: DummyID admission_dt discharge_dt 1 1/1/2018 2/28/2018 1 1/1/2018 1 1/27/2018 1/30/2018 1 3/15/2018 1 4/1/2018 1 4/7/2018 1 5/1/2018 1 6/1/2018 1 7/1/2018 1 8/1/2018 1 9/1/2018 1 10/1/2018 1 11/1/2018 1 12/1/2018 2 1/18/2018 5/22/2018 2 3/11/2018 4/30/2018 2 4/11/2018 6/22/2018

lizzy28 · ‎07-07-2022

Hi all, I want to identify groups through the column "flag" which takes values of missing and 1. When it is missing, it means that a new group starts from the observation; 1 means the same group as the last observation. Below is the sample data with the column "group" to be identified. Thank you! ID flag group 1 . 1 1 1 1 1 1 1 1 1 1 1 . 2 1 1 2 2 . 1 2 1 1 2 1 1 3 . 1 3 1 1 3 . 2

lizzy28 · ‎07-07-2022

Hi all, I need to count the number of inpatient stays and calculate length of stay per stay from admission date (admission_dt) and discharge date (discharge_dt). The tricky part is that when a new stay starts days after last stay but with the same provider, it is hard for me to separate the new stay from the previous one. Any suggestion would be greatly appreciated! Below is the example data, and the note column shows the counting and calculation rules. PERSON_ID provider_id admission_dt discharge_dt Note 1 A 8/1/2014 1/7/2018 a stay starts (admission_dt) from previous years, 1/1/2018 will be used as initial admission_dt 1 A 8/1/2014 1/10/2018 Overlapped stay, so it's considered as the same stay as the stay in last claim (last observation) 1 A 1/11/2018 1/31/2018 Consecutive date (date difference<=1) from last observation, so the same inpatient stay 1 A 1/11/2018 2/28/2018 1 A 1/11/2018 2/28/2018 1 A 1/11/2018 3/31/2018 1 A 1/11/2018 4/30/2018 1 A 5/16/2018 5/31/2018 Non-consecutive date from last admission, so a separate inpatient stay 1 A 5/16/2018 6/30/2018 1 A 5/16/2018 7/31/2018 1 A 5/16/2018 8/31/2018 1 A 5/16/2018 9/30/2018 1 A 5/16/2018 10/31/2018 1 A 5/16/2018 11/30/2018 1 A 5/16/2018 12/31/2018 2 B 11/1/2017 1/31/2018 2 B 12/24/2017 1/1/2018 2 B 11/1/2017 2/28/2018 2 B 11/1/2017 3/31/2018 2 B 11/1/2017 4/30/2018 2 B 11/1/2017 5/31/2018 2 B 11/1/2017 6/30/2018 2 B 11/1/2017 7/31/2018 2 B 11/1/2017 8/31/2018 2 B 11/1/2017 9/30/2018 2 B 11/1/2017 10/5/2018 2 C 10/6/2018 10/31/2018 Consecutive date from last admission but with different provider, so a separate inpatient stay 2 C 10/5/2018 11/30/2018 2 C 10/5/2018 12/31/2018 3 D 1/27/2018 1/30/2018 3 D 1/27/2018 2/28/2018 3 E 3/15/2018 A new inpatient stay at a different provider 15 days after the last stay 3 E 3/15/2018 Missing discharge date means the person is not discharged per claim, so 12/31/2018 will be used as the discharge date for the stay 3 E 3/15/2018 3 E 3/15/2018 3 E 3/15/2018 3 E 3/15/2018 3 E 4/1/2018 3 E 5/1/2018 3 E 6/1/2018 3 E 7/1/2018 3 E 8/1/2018 3 E 9/1/2018 3 E 10/1/2018 3 E 11/1/2018 3 E 12/1/2018

lizzy28 · ‎05-05-2022

Hi all, Is there a way to identify phone numbers with all the same digits in one phone number? For example, the phone numbers like 1111111111, 5555555555, 9999999999. Thanks a lot! Lizzy

lizzy28 · ‎12-02-2021

Here is a quick example of the lookup table: PROC_CD_ID PROC_CD PROC_CD_DESC PROC_CD_TYPE 7096 86376 MICROSOMAL ANTIBODIES, EACH C 7098 86382 NEUTRALIZATION TEST, VIRAL C 7099 86384 NITROBLUE TETRAZOLIUM DYE TE C 7100 86403 PARTICLE AGGLUTINATION; SCRE C 7102 86430 RHEUMATOID FACTOR; QUALITATI C The first field PROC_CD_ID is available in both the claim file and the lookup table while the second field PROC_CD is only available in the lookup table. I need to pull the field PROC_CD through joining PROC_CD_ID in both tables. Thank you!

lizzy28 · ‎12-02-2021

But procedure code ids are available in both files to join.

lizzy28 · ‎12-02-2021

Thank you, Kurt! But I need to use PROC SQL to pull data from a database. So data step doesn't work.

lizzy28 · ‎12-02-2021

I just need to pull all the procedure codes which are not available in the claims file. But procedure code ids are available in the claims file. Here is the full query: proc sql; create table claims_data as select distinct a.patient_id ,a.claim_num ,a.service_dt ,pr1.proc_1_cd ,pr2.proc_2_cd ,pr3.proc_3_cd ,pr4.proc_4_cd ,pr5.proc_5_cd ,pr6.proc_6_cd ,pr7.proc_7_cd ,pr8.proc_8_cd ,pr9.proc_9_cd ,pr10.proc_10_cd ,pr11.proc_11_cd ,pr12.proc_12_cd ,pr13.proc_13_cd ,pr14.proc_14_cd ,pr15.proc_15_cd ,pr16.proc_16_cd ,pr17.proc_17_cd ,pr18.proc_18_cd ,pr19.proc_19_cd ,pr20.proc_20_cd from claim_file a left join proc_lookup pr1 on a.proc_1_cd_id=pr1.proc_cd_id left join proc_lookup pr2 on a.proc_2_cd_id=pr2.proc_cd_id left join proc_lookup pr3 on a.proc_3_cd_id=pr3.proc_cd_id left join proc_lookup pr4 on a.proc_4_cd_id=pr4.proc_cd_id left join proc_lookup pr5 on a.proc_5_cd_id=pr5.proc_cd_id left join proc_lookup pr6 on a.proc_6_cd_id=pr6.proc_cd_id left join proc_lookup pr7 on a.proc_7_cd_id=pr7.proc_cd_id left join proc_lookup pr8 on a.proc_8_cd_id=pr8.proc_cd_id left join proc_lookup pr9 on a.proc_9_cd_id=pr9.proc_cd_id left join proc_lookup pr10 on a.proc_10_cd_id=pr10.proc_cd_id left join proc_lookup pr11 on a.proc_11_cd_id=pr11.proc_cd_id left join proc_lookup pr12 on a.proc_12_cd_id=pr12.proc_cd_id left join proc_lookup pr13 on a.proc_13_cd_id=pr13.proc_cd_id left join proc_lookup pr14 on a.proc_14_cd_id=pr14.proc_cd_id left join proc_lookup pr15 on a.proc_15_cd_id=pr15.proc_cd_id left join proc_lookup pr16 on a.proc_16_cd_id=pr16.proc_cd_id left join proc_lookup pr17 on a.proc_17_cd_id=pr17.proc_cd_id left join proc_lookup pr18 on a.proc_18_cd_id=pr18.proc_cd_id left join proc_lookup pr19 on a.proc_19_cd_id=pr19.proc_cd_id left join proc_lookup pr20 on a.proc_20_cd_id=pr20.proc_cd_id ;quit;

lizzy28 · ‎12-02-2021

Hi all, I need to pull the claims data with 20 procedure codes, and all the procedure codes are stored in a procedure code lookup table. Is there a simpler way to do that? My code: proc sql; create table claims_data as select distinct a.patient_id ,a.claim_num ,a.service_dt ,pr1.proc_1_cd ,pr2.proc_2_cd ,pr3.proc_3_cd ... ,pr20.proc_20_cd from claim_file a left join proc_lookup on pr1 on a.proc_1_cd_id=pr1.proc_cd_id left join proc_lookup on pr2 on a.proc_2_cd_id=pr2.proc_cd_id left join proc_lookup on pr3 on a.proc_3_cd_id=pr3.proc_cd_id ... ;quit; In the procedure code lookup table, there are fields like this: Field_num Field_Name 1 PROC_CD_ID 2 PROC_CD_CD 3 PROC_CD_DESC 4 PROC_CD_TYPE PROC_CD_ID is the primary key. Thank you all! Lizi

Online Status	Offline
Date Last Visited	‎09-09-2022 03:10 PM

Re: How to post and edit a question in the SAS Support Communities

Re: How to post and edit a question in the SAS Support Communities

Re: Include more inpatient stay info besides length of stay

Include more inpatient stay info besides length of stay

Re: Count number of inpatient stays and length of stay

Re: Identify overlapped timeframe across rows

Identify overlapped timeframe across rows

Loop through a column to identify groups

Count number of inpatient stays and length of stay

Identify phone numbers with all the same digits in one phone number

Re: Include more inpatient stay info besides length of stay

Re: Include more inpatient stay info besides length of stay

Re: Identify overlapped timeframe across rows

Re: Identify phone numbers with all the same digits in one phone numbe...

Re: Identify phone numbers with all the same digits in one phone numbe...

Interaction Plot

proc corr/reg by group

Re: Import zip csv file to SAS, Date variable values missing

Re: Change color of bars

Re: Change color of bars

Re: How to post and edit a question in the SAS Support Communities

Re: How to post and edit a question in the SAS Support Communities

Re: Include more inpatient stay info besides length of stay

Include more inpatient stay info besides length of stay

Re: Count number of inpatient stays and length of stay

Re: Identify overlapped timeframe across rows

Identify overlapped timeframe across rows

Loop through a column to identify groups

Count number of inpatient stays and length of stay

Identify phone numbers with all the same digits in one phone number

Re: How to loop into a lookup table

Re: How to loop into a lookup table

Re: How to loop into a lookup table

Re: How to loop into a lookup table

How to loop into a lookup table