conditional query with two datasets Help

[chuie]

Hi

I have two data sets ( clinic A and ED visits)

Clinic A sample:

ID    DATE        ICD

1      12/1           493

1      3/1             785

2      5/4             493

3      6/4            105

.

.

.

ED data sample

ID    DATE        ICD

1      1/1           785

1      4/12         493

2      6/4             493

3      7/14          105

.

.

.

what I am trying to match here is any ED encounter with  ICD codes macthed with the ICD of any encounter of that ID in clinic data set.

So on ED  data set I would like to create one variable called "sameicd" where if the ICD on ED data set match with any of the ICD on the cllinic A visits  data set then sameicd=yes, esle sameicd=no

for example  the ID 1 in 1/1 date has ICD 785 which matches with the second visit from clinic A in 3/1 then sameicd=yes

any help will be highly appriciated

Thanks

C

[LinusH]

SQL ED left join Clinic on all variables.
Use a case statement and test if Clinic.ICD is not missing.

[chuie]

Sorry I didnot understand what you meant. I was not asking to join the two tables but to create a case if the icd is same to any of the encounter to clinic in any of their visit.. please elaborate more  with actual code 

[LinusH]

This was pseudo code.
It seems you need to develop your SQL skills.

[chuie]

Yes ..that is why I ask for help

[LinusH]

If you will continue solving problems like this you definitely need a foundation of Base SAS and SQL. There are lot of training options available.
If I hand if over working code you would probably not be able to reuse it for other issues.

[chuie]

I simply got stuck on some data management so I posted for help.I think you
are thinking everyone taking this forum as granted but it's not true.There
are some people who just want how to do it rather lifetime lesson

[Patrick]

@chuie

I can understand that @LinusH's reply might come across a bit harsh to you. On the other hand I fully understand why @LinusH answered this way.

You can get in this forum a lot of very valuable help from very experienced people - and all free of charge.

To succeed in getting such help on an ongoing bases there are a few things expected from you.

1. Post sample data and post them in a way that the ones helping you don't have to do extra work for you (so post the data in the form of tested SAS data steps creating the data).

2. Show us the expected result (post another data step creating the desired result or post some table showing what the result should look like based on your sample source data).

3. Explain the logic how you get from your source sample data to the desired result

4. If you can, post your eventually not yet working code. That not only demonstrates that you've done your homework, it also shows us your line of thinking and the level of your SAS coding skills and allows us to provide you with targeted (coding) answers.

[chuie]

Thank you Patrik for clarification. I do belive that learning sas will help me tremendously on my job but at this moment  I am just seeking some expert guidance rather that asking somebody to do my job.

Also,

I did posted the sample data with explanation of what I want.

I am reposting this again with  a sample file of desired outcome.

I have two data sets ( clinic A and ED visits)

Clinic A sample:

ID    DATE        ICD

1      12/1           493

1      3/1             785

2      5/4             493

3      6/4            105

.

.

.

ED data sample

ID    DATE        ICD

1      1/1           785

1      4/12         493

2      6/4             493

3      7/14          105

.

.

.

what I am trying to match here is any ED encounter with  ICD codes macthed with the ICD of any encounter of that ID in clinic data set.

So on ED  data set I would like to create one variable called "sameicd" where if the ICD on ED data set match with any of the ICD on the cllinic A visits  data set then sameicd=yes, esle sameicd=no

for example  the ID 1 in 1/1 date has ICD 785 which matches with the second visit from clinic A in 3/1 then sameicd=yes

desired outcome:

ED data sample

ID    DATE        ICD  sameicd

1      1/1           785      yes

1      4/12         493      yes

2      6/4             493    yes

3      7/14          105     yes

We do not have NO because all of the ICD on ED  data matches with ICD of Clinic A data .

any help will be highly appriciated

Thanks

C

[Patrick]

@chuie

Please do your bit and provide sample data in a form which doesn't require us to do work for you which you can do yourself.

Providing data via a working data step could look like below. Please note that below code doesn't solve the issue that you're not providing us with full date strings. You will have to do something about this before you can reasonably import the dates as SAS Date values into variable DATS. Your turn!

data clinic;
  input ID DATE ICD;
  datalines;
1 12/1 493
1 3/1 785
2 5/4 493
3 6/4 105
;
run;

[chuie]

I couldn't paset it in actual sas code but here are my two data sets and the desired outcome data set. Thanks data clinic; input id date mmddyy10. icd; datalines; 1 12/1/2016 493 1 3/1/2018 785 2 5/4/2015 493 2 4/5/2016 651 3 6/4/2017 105 ; run; data ED; input id date mmddyy10. race $ gender$ icd; datalines; 1 1/1/2016 w f 785 1 4/12/2016 w f 493 2 6/4/2015 b m 493 3 7/14/2015 b f 105 3 3/4/2016 b f 359 ; run; data desire; input id date mmddyy10. race$ gender$ icd sameicd$; datalines; 1 1/1/2016 w f 785 yes 1 4/12/2016 w f 493 yes 2 6/4/2015 b m 493 yes 3 7/14/2015 b f 347 no 3 3/4/2016 b f 359 no ; run;

[Patrick]

Assuming there's been a typo for the ICD code in row 4 of table ED (which I've fixed in below data used), the following code returns your desired result

data clinic;
  input id date :mmddyy10. icd;
  datalines;
1 12/1/2016 493 
1 3/1/2018 785 
2 5/4/2015 493 
2 4/5/2016 651 
3 6/4/2017 105 
;
run;

data ED;
  input id date :mmddyy10. race $ gender$ icd;
  datalines;
1 1/1/2016 w f 785 
1 4/12/2016 w f 493 
2 6/4/2015 b m 493 
3 7/14/2015 b f 347 
3 3/4/2016 b f 359 
;
run;

data desire;
  input id date :mmddyy10. race$ gender$ icd sameicd$;
  datalines;
1 1/1/2016 w f 785 yes 
1 4/12/2016 w f 493 yes 
2 6/4/2015 b m 493 yes 
3 7/14/2015 b f 347 no 
3 3/4/2016 b f 359 no 
;
run;

proc sql;
  create table want as
  select
    E.*,
    case
      when missing(c.icd) then 'No'
      else 'Yes'
      end as sameicd
  from 
    ed as E
      left join
    (select distinct id, icd from clinic) as C
    on E.id=C.id and E.icd=C.icd
  ;
quit;

Depending on your actual data volumes and whether the data is stored in SAS files (tables) or a database, using a data step with a hash for lookup might perform quite a bit better.

[chuie]

Thank you so much.

Will you please navigate me thru the link or method's name  where I can self learn this process?