SAS / SQL help with arrays & joins
in [SAS]
|
From: ronchito on 20 Apr 2010 13:40 I have what I think is a pretty easy question. I have two medical tables that I'm working with. Table1 has one record per patient and each patient's record contains 15 diagnosis fields (DX1-DX15). Table2 contains extra information about the patients and could have multiple records per patient. (both tables use the same ID values) Is there a single-step way to simultaneously join these tables while also searching the DX1-15 fields in an array-like fashion? I know how to do this in multiple steps: Option A: Create a temp table by using an array in SAS to search the DX fields in Table1, then join to Table 2 using SQL -or- Option B: Create a temp table in SQL using 15 separate lines under the WHERE clause (one for each of the DX variables), then join the resulting table with Table 2. My question is: can I do the above in one single step? I suppose that would mean I'd either need to know how to replicate SAS' array capabilities in SQL, or how to join tables in SAS. It's not so difficult to do Option B since there are only 15 diagnoses, but in the future there could be variables that have, say, 250 iterations which would be a nightmare to list separately. Sorry if this is a basic question -- I'm okay at coming up with ways to get accurate final results, but I'd like to improve my efficiency if possible. Thanks.
From: montura on 20 Apr 2010 14:23 You might be able to transpose Table #2 to create a dataset with one record per ID, all information in that one record.
From: Reeza on 20 Apr 2010 16:23 On Apr 20, 10:40 am, ronchito <ronchito1...(a)gmail.com> wrote: > I have what I think is a pretty easy question. I have two medical > tables that I'm working with. Table1 has one record per patient and > each patient's record contains 15 diagnosis fields (DX1-DX15). Table2 > contains extra information about the patients and could have multiple > records per patient. (both tables use the same ID values) Is there a > single-step way to simultaneously join these tables while also > searching the DX1-15 fields in an array-like fashion? I know how to > do this in multiple steps: > > Option A: Create a temp table by using an array in SAS to search the > DX fields in Table1, then join to Table 2 using SQL > > -or- > > Option B: Create a temp table in SQL using 15 separate lines under > the WHERE clause (one for each of the DX variables), then join the > resulting table with Table 2. > > My question is: can I do the above in one single step? I suppose > that would mean I'd either need to know how to replicate SAS' array > capabilities in SQL, or how to join tables in SAS. It's not so > difficult to do Option B since there are only 15 diagnoses, but in the > future there could be variables that have, say, 250 iterations which > would be a nightmare to list separately. > > Sorry if this is a basic question -- I'm okay at coming up with ways > to get accurate final results, but I'd like to improve my efficiency > if possible. > > Thanks. How would like the duplicates in Table B accounted for? The results are a one to many match? If so use a merge in sas. Something like below (untested). data want; merge tablea tableb; by uniqid; array dx(15) dx1-dx15; run;
From: ronchito on 21 Apr 2010 15:46 thanks for the replies -- i wasn't familiar with the MERGE function before. can i assume that there's no equivalent to the SAS array function in SQL?
From: Richard A. DeVenezia on 27 Apr 2010 13:38
On Apr 20, 1:40 pm, ronchito <ronchito1...(a)gmail.com> wrote: > I have what I think is a pretty easy question. I have two medical > tables that I'm working with. Table1 has one record per patient and > each patient's record contains 15 diagnosis fields (DX1-DX15). Table2 > contains extra information about the patients and could have multiple > records per patient. (both tables use the same ID values) Is there a > single-step way to simultaneously join these tables while also > searching the DX1-15 fields in an array-like fashion? What kind of search or search criteria are you trying to implement ? -- Richard A. DeVenezia http://www.devenezia.com |