About Lefty

Lefty · ‎09-19-2024

Hi, I am trying to create a time-varying covariate that indicates whether someone was taking drug 1 (drugcat=0), drug 2 (drugcat=1), or drug 1+ drug 2 (drugcat=2). I want to run a Cox proportional hazards regression (proc phreg) that incorporates the fact that the drugs people are taking changes over time. Here is a simplified version of the data I have: ID Drug1StartDate1 Drug1EndDate1 Drug1StartDate2 Drug1EndDate2 Drug2StartDate1 Drug2EndDate1 Drug2StartDate2 Drug2EndDate2 age sex censor 1 June 1 2011 July 1 2011 50 0 0 2 Jan 1 2012 Dec 31 2012 June 1 2012 Dec 31 2012 40 1 1 3 Jan 1 2010 June 15 2010 July 1 2010 Dec 1 2010 Feb 1 2010 June 30 2010 Sept 15 2010 Dec 15 2010 60 1 0 I want the start and stop times in days that they are in each drug category. It’s not so hard when someone falls into only one category (ID=1) or two categories (ID=2) but it gets really tricky when people move between the categories (ID=3 is on only drug 1 from Jan 1-Jan 31, then on both drugs from Feb 1 to June 15, then on only drug 2 from June 16-30, then back to drug 1 from July 1 to Sept 14, then on both drugs from Sept 15 to Dec 1, then only on Drug 2 from Dec 2 to Dec 15. Some people have up to 20 start/stop dates so I can't really muscle through each possible variation. Here is what I'd like my data to look like: ID Start Stop Drugcat age sex censor 1 0 30 0 50 0 0 2 0 152 0 40 1 0 2 152 365 2 40 1 1 3 0 31 0 60 1 0 3 31 167 2 60 1 0 3 167 182 1 60 1 0 3 182 257 0 60 1 0 3 257 334 2 60 1 0 3 334 365 1 60 1 0 Ultimately I’ll use this code to run my regression (where drugcat=0 means drug 1 alone, drugcat=1 means drug 2 alone and drugcat=3 means both drugs at the same time): Proc phreg data=want; Class drugcat; Model (start, stop)*censor(0)=drugcat age sex/rl; Run; I’ve tried using arrays to get the data I want but it quickly falls apart when someone switches between the drug categories more than once. Can anyone tell me a better way to get to my goal? Thanks in advance!

Lefty · ‎09-28-2023

This is even better than I hoped, thank you so much!

Lefty · ‎09-28-2023

Thanks Reza! Is there a way to do this if I want crosstabs, so tables (sex age) * race; for example?

Lefty · ‎09-28-2023

Thanks, yes, this is true, maybe I should have started my question there. Here's the original code I did: ods output crosstabfreqs=freqs; proc freq data=mydata; format sex sexf. region regionf.; tables (sex age region)*stratvar/norow nopercent;run; And then my ultimate goal is to get the table I listed in my original post. If there's a better ods table to use, I'd love to know about it. Thank you again!

Lefty · ‎09-28-2023

Thanks. When I do the coalesce, it gives me the values of each column, like this: Frequency ColPercent Variable 10 50 1 10 50 0 12 60 0 8 40 1 15 75 1 5 25 0 Is there a way to have the text of the variables come out instead?

Lefty · ‎09-27-2023

Hi, I am using SAS 9.4. I have data that look like this: Frequency ColPercent Sex Age Region 10 50 Male . . 10 50 Female . . 12 60 . Young . 8 40 . Old . 15 75 . . West 5 25 . . East And I would like it to look like this: Frequency ColPercent Variable 10 50 Male 10 50 Female 12 60 Young 8 40 Old 15 75 West 5 25 East I know I could do something like: if sex ne . then Variable=sex but I actually have ~100 variables so that would take a long time. Is there a more efficient way accomplish what I want? Thanks in advance!

Lefty · ‎09-09-2022

Amazing! Works perfectly. Thank you!!

Lefty · ‎09-09-2022

Hi, I am using SAS 9.4. I have wide data of all the drugs used by patients that looks like this: ID Drug1 Drug2 Drug3 Drug4 Drug5 Drug6 Drug7 1 Ceftazidme Ciprofloxacin 2 Ceftazidme Colistin Vancomycin 3 Cefazolin Ceftazidme Meropenem 4 Cefepime Minocycline Colistin … I would like to be able to tell how many people used each type of drug no matter which column the drugs are in, which is hard to do with the way my data are now (e.g., I want to know that 3 people used Ceftazidme, 2 people used Colistin, etc). I would like my data to look like this: ID Ceftazidme Ciprofloxacin Colistin Vancomycin Cefazolin Meropenem Cefepime Minocycline … Drug45 1 1 1 0 0 0 0 0 0 2 1 0 1 0 0 0 0 0 3 1 0 0 0 1 1 0 0 4 0 0 1 0 0 0 1 1 … Although each ID had a maximum of 7 drugs, there are 45 unique drug names among all the IDs, so it's a little unwieldy to write out arrays for each drug name. Any ideas would be great! Thanks in advance.

Lefty · ‎03-15-2022

Holy cow that is so much easier and faster than the sql way! Thank you thank you- I was not familiar with hash but it is amazing!

Lefty · ‎03-15-2022

Thanks for your quick reply! The issue is, my patient data has 12 diagnosis codes per row. The dataset is 15GB so I'd prefer not to transpose the data from wide to long so I only have 1 diagnosis code. Would it make sense to I modify your sql code like this: on a.dxcode=b.dxcode1 or a.dxcode=b.dxcode2 etc?

Lefty · ‎03-15-2022

Multiple diseases could be flagged and I actually do want to capture that because I want to know which dates the diseases were flagged.

Lefty · ‎03-15-2022

Yes, exactly!

Lefty · ‎03-15-2022

Hi, I am trying to combine two datasets using Base SAS 9.4. The first "master" dataset is laid out like this: DxCode Disease1 Disease2 Disease3 … Disease17 B180 0 0 1 … 0 B181 0 0 1 … 0 B188 0 0 1 … 0 etc I want to combine it with a "patient" data file that looks like this: PatientID Date DxCode1 DxCode2 … DxCode12 1 1/1/2017 A180 B180 D43 1 1/31/2017 C059 C060 I26 1 5/1/2017 D10 C061 D33 1 5/1/2017 E123 U32 S23 2 5/1/2017 I27 S44 P55 2 5/1/2017 S43 I28 B188 I want to include only rows of data from the "patient" file where at least one of the values of the variables DX1-DX12 is found in the "master" file and I want to know which disease that DX1-DX12 corresponds to. Ultimately, I would like the resulting data to look like this (and it could have many rows per patient if they have multiple diseases on different dates): PatientID Date Disease1 Disease2 Disease3 … Disease17 1 1/1/2017 0 0 1 … 0 2 5/1/2017 0 0 1 … 0 I don't care which "dx" field the disease appeared in or which particular dx code the patient had, just whether or not a dx code within each disease was diagnosed on a particular date. I was thinking I'd use proc sql, like this: proc sql; create table want as select a.*, b.patientID, b.date from master as a inner join patient as b on a.dxcode=b.dxcode1 or b.dxcode2 or b.dxcode3 or b.dxcode4 or b.dxcode5 or b.dxcode6 or b.dxcode7 or b.dxcode8 or b.dxcode9 or b.dxcode10 or b.dxcode11 or b.dxcode12; But that is taking forever (my patient file is very large!) and I'm getting the error about "The execution of this query involves performing one or more Cartesian product joins that cannot be optimized" I'm hoping there's a more efficient way to code this. Thank you so much in advance!

Lefty · ‎01-15-2020

Amazing!! It works perfectly. I am so appreciative of your help, I never would have figured that out on my own! Thank you!

Lefty · ‎01-15-2020

Ah, I see. The reason your example does not quite do what I'm looking for is because the second 5 in the 2 5 row should NOT match to the first 5 in the 5 8 row. I want only equal values of match_1 to match to each other, and only equal values of match_2 to match to each other. So for example in this wrong triplet, the match_2 in the first two rows is correct, but match_1 in the second two rows is not equal and therefore incorrect: ID case match_1 match_2 26229681701 0 100000025 1549255201 1 100000021 100000025 28654518402 0 100000025 I would instead like it to look like this: ID case match_1 match_2 26229681701 0 100000025 1549255201 1 100000021 100000025 2280537501 0 100000021 Does that help? Thank you!!! @novinosrin wrote: If you could post some expected correct ones for the incorrect ones, that would help. What I means to ask or say is, should you have something like the below 1 2 5 8 3 9 2 5 Let's begin matching Take 1, look up , no matches found. Since this is starting, increment case counter =1; Take 2, perform look up, look forward 3 records where you find a match with 4th records 2 5, so case counter for 1 2 and 2 5 is 1. However 5 matches with 5 8 in 2nd record, so again the case counter is still 1 for 1 2, 2 5, 5 8 . Next starting from 3 9, is a new start, so increment case counter by 1, so case counter=case counter +1 which is equal to 2. Repeat the process again until all records are scanned. Is my understand correct?

Coding time varying covariate for Cox proportional hazards regression

Re: Compiling non-missing data across columns into one column

Re: Compiling non-missing data across columns into one column

Re: Compiling non-missing data across columns into one column

Re: Compiling non-missing data across columns into one column

Compiling non-missing data across columns into one column

Re: Organizing wide string data into binary columns

Organizing wide string data into binary columns

Re: Combining datasets one to many where many dataset is wide

Re: Combining datasets one to many where many dataset is wide

Re: Combining datasets one to many where many dataset is wide

Re: Combining datasets one to many where many dataset is wide

Combining datasets one to many where many dataset is wide

Re: Creating variable that links rows from 1:2 match

Re: Creating variable that links rows from 1:2 match