Understanding SQL Code

[Ranjeeta]

Hello 

Would appreciate if someone can help me understand the code below

There are 25 dx codes in the p970_138_dadsdsrecs database 

Is the code below essentially creating 25 tables where dx10code1 to dx10code25 are not blank setting them together and finding the cases where the dx condition is found in the 25 codes (i.e. in the step bolded)

Would this be equivalent to creating an array of the 25 codes and then finding those bolded dx codes?

proc sql;
create table dad_dx_all1 as
select id, key_enc, epi_enc, 1 as seq, dx10code1 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype1 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code1 is null
;quit;

proc sql;
create table dad_dx_all2 as
select id, key_enc, epi_enc, 2 as seq, dx10code2 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype2 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code2 is null
;quit;

proc sql;
create table dad_dx_all3 as
select id, key_enc, epi_enc, 3 as seq, dx10code3 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype3 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code3 is null
;quit;

proc sql;
create table dad_dx_all4 as
select id, key_enc, epi_enc, 4 as seq, dx10code4 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype4 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code4 is null
;quit;

proc sql;
create table dad_dx_all5 as
select id, key_enc, epi_enc, 5 as seq, dx10code5 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype5 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code5 is null
;quit;

proc sql;
create table dad_dx_all6 as
select id, key_enc, epi_enc, 6 as seq, dx10code6 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype6 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code6 is null
;quit;

proc sql;
create table dad_dx_all7 as
select id, key_enc, epi_enc, 7 as seq, dx10code7 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype7 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code7 is null
;quit;
proc sql;
create table dad_dx_all8 as
select id, key_enc, epi_enc, 8 as seq, dx10code8 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype8 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code8 is null
;quit;
proc sql;
create table dad_dx_all9 as
select id, key_enc, epi_enc, 9 as seq, dx10code9 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype9 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code9 is null
;quit;
proc sql;
create table dad_dx_all10 as
select id, key_enc, epi_enc, 10 as seq, dx10code10 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype10 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code10 is null
;quit;
proc sql;
create table dad_dx_all11 as
select id, key_enc, epi_enc, 11 as seq, dx10code11 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype11 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code11 is null
;quit;
proc sql;
create table dad_dx_all12 as
select id, key_enc, epi_enc, 12 as seq, dx10code12 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype12 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code12 is null
;quit;
proc sql;
create table dad_dx_all13 as
select id, key_enc, epi_enc, 13 as seq, dx10code13 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype13 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code13 is null
;quit;
proc sql;
create table dad_dx_all14 as
select id, key_enc, epi_enc, 14 as seq, dx10code14 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype14 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code14 is null
;quit;
proc sql;
create table dad_dx_all15 as
select id, key_enc, epi_enc, 15 as seq, dx10code15 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype15 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code15 is null
;quit;
proc sql;
create table dad_dx_all16 as
select id, key_enc, epi_enc, 16 as seq, dx10code16 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype16 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code16 is null
;quit;
proc sql;
create table dad_dx_all17 as
select id, key_enc, epi_enc, 17 as seq, dx10code17 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype17 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code17 is null
;quit;
proc sql;
create table dad_dx_all18 as
select id, key_enc, epi_enc, 18 as seq, dx10code18 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype18 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code18 is null
;quit;
proc sql;
create table dad_dx_all19 as
select id, key_enc, epi_enc, 19 as seq, dx10code19 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype19 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code19 is null
;quit;
proc sql;
create table dad_dx_all20 as
select id, key_enc, epi_enc, 20 as seq, dx10code20 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype20 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code20 is null
;quit;
proc sql;
create table dad_dx_all21 as
select id, key_enc, epi_enc, 21 as seq, dx10code21 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype21 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code21 is null
;quit;
proc sql;
create table dad_dx_all22 as
select id, key_enc, epi_enc, 22 as seq, dx10code22 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype22 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code22 is null
;quit;
proc sql;
create table dad_dx_all23 as
select id, key_enc, epi_enc, 23 as seq, dx10code23 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype23 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code23 is null
;quit;
proc sql;
create table dad_dx_all24 as
select id, key_enc, epi_enc, 24 as seq, dx10code24 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype24 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code24 is null
;quit;
proc sql;
create table dad_dx_all25 as
select id, key_enc, epi_enc, 25 as seq, dx10code25 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype25 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code25 is null
;quit;

/*Combine all of the above tables into one*/
data pa.dad_dx_all;
set dad_dx_all1 dad_dx_all2 dad_dx_all3 dad_dx_all4 dad_dx_all5 dad_dx_all6 dad_dx_all7 dad_dx_all8 dad_dx_all9 dad_dx_all10
dad_dx_all11 dad_dx_all12 dad_dx_all13 dad_dx_all14 dad_dx_all15 dad_dx_all16 dad_dx_all17 dad_dx_all18 dad_dx_all19 dad_dx_all20
dad_dx_all21 dad_dx_all22 dad_dx_all23 dad_dx_all24 dad_dx_all25;
run;

proc sql;
create table pa.non_stemi_ami_cihi as
select distinct key_enc from
pa.dad_dx_all
where key_enc in (select key_enc from pa.dad_dx_all
where dx in ('I210', 'I211', 'I212', 'I213', 'I214', 'I2140', 'I2141', 'I2142', 'I2149',
'I219', 'I220', 'I221', 'I228', 'I229', 'I240'))
and key_enc in (select key_enc from pa.dad_dx_all
where not dx in ('R9430'))
;quit;

[Reeza]

Essentially yes.

If you want to stick with this program for further on processing, you can replace the SQL queries with a PROC TRANSPOSE instead, and then use the remaining program from there.

[PaigeMiller]

It's hard to see any particular commonality between the not-bolded code and the bolded-code. The bolded code tests for dx in 'I210', but no similar test appears in the un-bolded code.

So how could we know if it produces the same result? It doesn't seem like it is doing the same thing.

I also fail to see any point in splitting idave.p970_138_dadsdsrecs into 25 different data sets and then combining them. Why not just keep all of idave.p970_138_dadsdsrecs and work from there?

By the way, if the question is: do the two different blocks of code produce equivalent results, the answer really should be: please try it and find out yourself.

[Reeza]

@PaigeMiller I don't believe this is a code comparison. OP is primarily concerned if the bolded section can be replaced with an array. 

The code transposes the data via 25 PROC SQL steps.

Appends table in first data step.

Filters it in final proc sql. 

OP, to make this simpler, use PROC TRANSPOSE to replace 25 proc sql's and one data step and use last proc sql only. 

[Ranjeeta]

Hello Reeza,

Would really appreciate some help here in understanding the code 

but can you explain or write the proc transpose step to do the same

As i thought that this code is trying to select a few fields from the idave.p970_138_dadsdsrecs and remove any fields where for e.g dx10code25 is blank 

But will i be able to achieve the same using just an array step to look for the specific dx codes if they are present in any of the 25 dx fields - then i dont need to create 25 datatsets but what is 25 as seq doing in the code below?

proc sql;
create table dad_dx_all25 as
select id, key_enc, epi_enc, 25 as seq, dx10code25 as dx, case when source = 'I' then inst when source = 'S' then aminst end as site, source, dxtype25 as dx_type
from idave.p970_138_dadsdsrecs
where not dx10code25 is null
;quit;

[Reeza]

The blank part won't be filtered out, but you could transpose using a data step. You can change the code to use an array but then you have to re-write the code.

My thought was changing the first part only would save you more time and avoid any issues with code conversion and extra testing needed.

I thought I did explain the code, can you clarify exactly what you don't understand then and I can try and explain those concepts. I'm happy to help, but I think you should try the TRANSPOSE or via a data step and post back if you have issues with it. I don't have data to work with so odds are I"m getting the code wrong anyways.

In general, medicine likes to store the data in a wide format because that's required for regressions. However, the long format is better for filtering and doing condition counts and determining how many people have each type of diagnosis.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/