Hi Team,
Can some one please help me interpret what this below query means?
PROC sql;
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
LEFT JOIN
testtable B
on A.DX=B.ICD10
WHERE COALESCE(B.smm,0)=0
group by mbr_ID,calculated DX
;
quit;
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?
I am getting very few values(like 15-16 values in total) under DX column rest all are comming blanck.I was expecting many hundreds of values under DX column
Also,Can you please help me explain below two commands
Coalesce(B.ICD9, ' ')
Coalesce(B.SMM,0)=0
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.
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.
Not in front of a computer but my guess is the SQL version is different?
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.
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.
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;
Oh **bleep** - I misremembered. I apologise.
I was trying to say d__n, by the way, in case you thought I was aiming for something stronger.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.