01-31-2017 08:48 PM
Can some one please help me interpret what this below query means?
Create table tmp as
select mbr_id, CASE WHEN qualifier='2' THEN COALESCE(B.ICD9,' ') ELSE A.DX END AS DX
from INP_1 A
group by mbr_ID,calculated DX
01-31-2017 08:55 PM
It's essentially a lookup from one table to another, and creating a new DX variable. If the qualifier is 2, DX comes from table B, otherwise it's from table A.
Is is there a specific issue you're having?
01-31-2017 09:14 PM
The first one shouldn't work actually, should be COALESCEC (extra C)
From the ever so helpful documentation
Coalesce -> Returns the first nonmissing value from a list of numeric arguments.
01-31-2017 09:18 PM
Oh it works fine, but I agree, it should be coalescec.
62 proc sql; 63 create table class_coalesce as 64 select coalesce(name, 'xxx') as name length=30 65 from sashelp.class; NOTE: Table WORK.CLASS_COALESCE created, with 19 rows and 1 columns.
02-01-2017 04:31 AM
Not sure thats right on the coalesce. Coalesce is an SQL function, returns the first non-missing of a list of variables irrespective of their data type. So in proc sql that shoudl work fine.
01-31-2017 09:01 PM - edited 01-31-2017 09:05 PM
It's not very well formed, but I'll give it ago. (Translated: I wouldn't have written it quite like that)
Your master table, inp_1, contains at least three columns: mbr_id, qualifier and dx. It is being joined with testtable (I hate tables called that - sorry if it's yours, but I really do!) which contains icd10, icd9 and smm.
The target table will contain inp_1's mbr_id and either icd_9 or space (where icd_9 is missing or the join failed) if qualifier is 2, or dx.
The where clause is possibly problematic. If a join against testtable can't be found, it will always be true because of the coalesce: smm will be missing, so the function will return 0. However if the join is successful and smm is still missing, again it will be true.
The group by is wrong too: there's so grouping of variables. Turn that into an order by. The SQL processor will return a warning message to this effect.
But without seeing the data, I can only ever guess.
01-31-2017 09:42 PM
01-31-2017 10:02 PM
I don't think that's true, but also realizing that using COALESCE with a missing argument doesn't make sense.
I tested COALESCE, in a data step with character variables it does throw errors in the log.
data class; set sashelp.class; if _n_ in (5, 10, 15) then call missing(sex, age, weight, height); run; proc sql ; create table want_sql as select age, name, sex, coalesce(age, 999) as n_age, coalesce(sex, 'U') as c_sex from class; quit; proc print data=want_sql; run; data want_ds; set class; n_age=coalesce(age, 999); c_sex=coalesce(sex, 'U'); run; proc print data=want_ds; run;
02-01-2017 04:41 AM
First, write the code in a nicely formatted manner. This will help you, and others read your code and understand more easily:
proc sql; create table TMP as /* Selection phase. Details what columns to have in the output table */ select MBR_ID, /* If condition is true then take B.ICD9, otherwise take missing - which is nonsense */ case when QUALIFIER='2' then coalesce(B.ICD9,' ') else A.DX end as DX /* Source sections. Details what tables to take data from */ from INP_1 A left join TESTTABLE B on A.DX=B.ICD10 /* This section filters so only data where B.SMM is 0 or missing */ where coalesce(B.SMM,0)=0 /* Group/order - this section is irrelevant as no data grouping or aggregation is done in selection */ group by MBR_ID, CALCULATED DX; quit;
So to re-write the query:
proc sql; create table TMP as select MBR_ID, case when QUALIFIER='2' then B.ICD9 else A.DX end as DX from INP_1 A left join (select * from TESTTABLE where SMM > 0) B on A.DX=B.ICD10; quit;