How to find all the records of certain ID with a certain characteristics

[Primavera]

There is no rush. You have helped me a lot already. I hope you have a
restful weekend after long workdays 🙂

[novinosrin]

To obtain longest episode, you can just take advantage of epi_duration variable. To wit, the max of the epi_duration group by ID would indeed give you the longest episode. IMHO, There is no challenge there. If there are ties, you should probably deliberate with your team and  address them accordingly.

proc sql;
create table want as
select *
from have
group by id
having max(epi_duration)=epi_duration;
quit;

And to identify the first qualifying episode of each participant 
if some participants have more than 1 qualifying episodes)? You could use the automatic by group marker variables First and last

data want;
 set have;
 by id Epi_start;
 if first.Epi_start and not last.Epi_start;
run;

[Primavera]

Thank you once again for answering my questions. It is amazing how much
more efficient SQL is compared to ordinary SAS coding. Cheers,

[novinosrin]

HI @Primavera  I'm glad you are finding the community helpful. On the community, people like Reeza and few others have noticed my proficiency right from being a newbie to where I am. Therefore, I really like that you ask questions and ponder in wanting to know how stuff works in terms of the logic. I would also recommend reading @data_null__ 's posts as I did over the last 4-5 years that actually made the difference to my learning. Of course, to each their own, however it's a personal opinion and exclusively that is something worked for me. 

In terms of work experience, I must confess I really do not have much of work experience at all and sometimes I wonder whether I have to fill my CV/Resume with some nonsense just to make it look better. lol. My unconventional life reflects my approach in programming too i.e. actually imagination of sorts akin to what I would deem as a progression from Nintendo, Sega, Play station, X-Box and now SAS(Smiles) 🙂  All the Best!

[Primavera]

Hi Novinosrin,

I hope you had a good summer and are safe and well. 

I am back to ask for your help again. I hope that is OK with you.

This time I am trying to eliminate some records based on a flag (cancer= 0 or 1). So, I was able to link my data set with another one and find out various diagnosis that people in my data set had. Now I want to eliminate all the records for people who have had a cancer diagnosis in their records (cancer=1). It seamed very simple and I used:

proc sql;
create table nocancers as
select *
from alldiagnosis 
where Rcpt_ID in (select Rcpt_ID from alldiagnosis where cancer ne 1);
quit;

The code did not really work. It got rid of some records but a lot of cancer=1 observations were left in there. This is very odd for me because other variations of the code worked like a charm (like selecting all the records of people of certain age or all the records after a certain date). The cancer flag is of numeric type, BEST12 format and length 8.  

Any advice would be appreciated 🙂 

[novinosrin]

Hi @Primavera  You wrote "This time I am trying to eliminate some records based on a flag (cancer= 0 or 1)"--> So, how do you expect -

where Rcpt_ID in (select Rcpt_ID from alldiagnosis where cancer ne 1); (Not Equal to 1)

to work?  🙂

I would expect you to write 

where Rcpt_ID in (select Rcpt_ID from alldiagnosis where cancer not in (0,1);

[Primavera]

Hi again @novinosrin ,

Thank you very much for getting back to me. I tried your solution but it did not work. 

proc sql;

create table nocancer as
select *
from alldignosis
where Rcpt_ID in (select Rcpt_ID from alldiagnosis where cancer not in (0,1));

The resulting data set was empty! I  am a bit confused with not in (0,1) concept. I want all the records of people with even a single instance of cancer=1 diagnosis to be removed. I don't understand how not in (0,1) achieves that.

I know how to gather all the records of people with even one record of cancer=1 :

proc sql;
create table cancerdiag as
select *
from alldiagnosis 
where Rcpt_ID in (select Rcpt_ID from alldiagnosis group by Rcpt_ID having max(cancer));
quit;

But I want a data set that is exactly everyone else except those in the above (cancerdiag) data set. I think one option is to give cancerdiag data set a flag and then merge with the main alldiagnosis data set and then delete all the records that have the flag.  But that is an ugly solution 😞

[novinosrin]

HI @Primavera  Please post a small sample of your data with the expected output.  Data speaks for better communication than words. 🙂

[Primavera]

Hi @novinosrin 

alldiagnosis.csv is a sample of data that I have. cancereliminated.csv is the output I want. All the records of the individuals that had cancer=1 somewhere in their records in alldiagnsis completely removed from cancereliminated file. Hope that helps.

[novinosrin]

Can you paste the data as plain text plz?

[Primavera]

Hi @novinosrin,

I hope this one works. Here is the input file:

Rcpt_Anon_ID birthdate DSPN_DATE DSPN_DAY_SUPPLY_QTY dspns_age Opioid_start Opioid_end Total_dose Total_days DSPN_COUNT Cancer
003115006 1995-07-01 2012-12-12 2 17.45106092 2010-09-28 2012-12-25 820 55 0
006093226 1994-06-01 2010-05-25 3 17.45106092 2010-09-28 2012-12-25 820 55 1
006093226 1994-06-01 2012-01-25 2 17.65092402 2011-12-09 2012-02-01 55 3 1
006093226 1994-06-01 2014-08-19 2 17.65092402 2011-12-09 2012-02-01 55 3 1
006927026 1993-11-01 2007-05-01 4 17.65092402 2011-12-09 2012-02-01 55 3 0
006927026 1993-11-01 2011-06-17 7 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2010-04-14 17 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2012-07-07 4 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2015-10-18 2 17.62354552 2010-06-02 2011-08-25 450 14 0
010004336 1997-01-01 2006-11-14 1 17.62354552 2010-06-02 2011-08-25 450 14 0
010004336 1997-01-01 2006-11-14 46 17.62354552 2010-06-02 2011-08-25 450 14 0
010004336 1997-01-01 2008-02-28 19 17.62354552 2010-06-02 2011-08-25 450 14 0
010004336 1997-01-01 2008-03-17 3 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2008-03-19 27 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2009-04-27 4 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2010-11-04 2 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2012-03-14 1 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2013-09-22 83 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2014-01-11 2 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2014-01-12 109 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2015-05-24 7 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2015-05-30 4 17.62354552 2010-06-02 2011-08-25 450 14 1
010004336 1997-01-01 2015-06-02 29 17.62354552 2010-06-02 2011-08-25 450 14 1
010682226 1994-05-01 2007-01-12 1 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-01-23 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-03-08 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-07-26 9 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-02-22 11 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-06-25 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-08-31 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-10-16 16 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-01-05 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-02-23 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-05-05 4 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-05-13 8 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-06-10 63 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-09-01 15 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-09-30 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-10-07 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-11-06 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-11-11 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-06-25 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-07-30 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-08-23 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-09-08 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2011-07-04 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2011-12-23 2 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2012-05-26 19 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-03-05 3 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-08-07 10 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-09-07 4 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-07-20 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-08-16 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-10-16 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-11-29 71 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2011-11-11 29 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2012-01-14 41 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2012-12-26 3 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2014-03-19 41 16.79671458 2014-02-07 2014-04-28 81 2 0

Here is the output: (I am also going to attach both files)
Rcpt_Anon_ID birthdate DSPN_DATE DSPN_DAY_SUPPLY_QTY dspns_age Opioid_start Opioid_end Total_dose Total_days DSPN_COUNT Cancer
003115006 1995-07-01 2012-12-12 2 17.45106092 2010-09-28 2012-12-25 820 55 0
006927026 1993-11-01 2007-05-01 4 17.65092402 2011-12-09 2012-02-01 55 3 0
006927026 1993-11-01 2011-06-17 7 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2010-04-14 17 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2012-07-07 4 17.62354552 2010-06-02 2011-08-25 450 14 0
009232706 1996-02-01 2015-10-18 2 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-01-12 1 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-01-23 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-03-08 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2007-07-26 9 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-02-22 11 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-06-25 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-08-31 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2008-10-16 16 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-01-05 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-02-23 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-05-05 4 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-05-13 8 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-06-10 63 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-09-01 15 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-09-30 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-10-07 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-11-06 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2009-11-11 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-06-25 6 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-07-30 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-08-23 5 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2010-09-08 3 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2011-07-04 10 17.62354552 2010-06-02 2011-08-25 450 14 0
010682226 1994-05-01 2011-12-23 2 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2012-05-26 19 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-03-05 3 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-08-07 10 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2013-09-07 4 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-07-20 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-08-16 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-10-16 5 17.64544832 2011-09-14 2012-02-03 143 7 0
010682226 1994-05-01 2014-11-29 71 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2011-11-11 29 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2012-01-14 41 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2012-12-26 3 17.64544832 2011-09-14 2012-02-03 143 7 0
015817336 1997-06-01 2014-03-19 41 16.79671458 2014-02-07 2014-04-28 81 2 0

[novinosrin]

Hi @Primavera  I believe you are trying to do this -

proc sql;
create table want as
select *
from have
group by Rcpt_Anon_ID
having not max(cancer);
quit;

Please try and see

[Primavera]

Thank you very much @novinosrin . As always your solution works. I only needed to sort again afterwards to get what I needed. I had a code very similar to your code at first but it did not work due to a very silly mistake.  I fixed that and that one works now too:

proc sql;
create table want as
select *
from have 
where Rcpt_Anon_ID in (select Rcpt_Anon_ID from have group by Rcpt_Anon_ID having not max(Cancer));
quit;

I hate it when I almost have the solution and it does not work because of stupidity making me waste a few hours looking for another solution! 

As always, thank you very much for putting up with my questions and helping me 🙂

[Reeza]

Add an ORDER BY in your SQL to avoid another sort.

[Primavera]

Thanks for a great suggestion. Worked like a charm.