topic Re: conditional query with two datasets Help in SAS Data Management

conditional query with two datasets Help

chuie — Mon, 12 Mar 2018 15:48:03 GMT

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

Re: conditional query with two datasets Help

LinusH — Mon, 12 Mar 2018 16:38:26 GMT

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

Re: conditional query with two datasets Help

chuie — Mon, 12 Mar 2018 16:58:37 GMT

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

Re: conditional query with two datasets Help

LinusH — Mon, 12 Mar 2018 18:51:53 GMT

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

Re: conditional query with two datasets Help

chuie — Mon, 12 Mar 2018 19:25:37 GMT

Yes ..that is why I ask for help

Re: conditional query with two datasets Help

LinusH — Tue, 13 Mar 2018 07:14:26 GMT

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.

Re: conditional query with two datasets Help

chuie — Tue, 13 Mar 2018 12:28:34 GMT

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

Re: conditional query with two datasets Help

Patrick — Tue, 13 Mar 2018 12:48:12 GMT

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

Re: conditional query with two datasets Help

chuie — Tue, 13 Mar 2018 14:26:28 GMT

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

Re: conditional query with two datasets Help

Patrick — Wed, 14 Mar 2018 00:38:45 GMT

@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;

Re: conditional query with two datasets Help

chuie — Wed, 14 Mar 2018 01:40:09 GMT

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;

Re: conditional query with two datasets Help

Patrick — Wed, 14 Mar 2018 08:09:16 GMT

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.

Re: conditional query with two datasets Help

chuie — Wed, 14 Mar 2018 14:19:37 GMT

Thank you so much.

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