DATA Step, Macro, Functions and more

Query Explanation needed

Reply
Contributor
Posts: 20

Query Explanation needed

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;

 

Super User
Posts: 17,963

Re: Query Explanation needed

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? 

Contributor
Posts: 20

Re: Query Explanation needed

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

Contributor
Posts: 20

Re: Query Explanation needed

Also,Can you please help me explain below two commands

Coalesce(B.ICD9, ' ')

Coalesce(B.SMM,0)=0

Super User
Posts: 17,963

Re: Query Explanation needed

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.

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p1vjttz6nua...

 

 

Super Contributor
Posts: 251

Re: Query Explanation needed

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.

 

Super User
Posts: 17,963

Re: Query Explanation needed

Not in front of a computer but my guess is the SQL version is different? 

Super User
Super User
Posts: 7,432

Re: Query Explanation needed

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.

Super Contributor
Posts: 251

Re: Query Explanation needed

[ Edited ]

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_idqualifier and dx. It is being joined with testtable (I hate tables called that - sorry if it's yours, but I really do!) which contains icd10icd9 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 coalescesmm will be missing, so the function will return 0. However if the join is successful and smm is still missingagain 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.

Super Contributor
Posts: 251

Re: Query Explanation needed

Nah - data step the same. I like to be explicit, because it makes the code less ambiguous. But it's not necessary. It doesn't work the other way though, of course!
Super User
Posts: 17,963

Re: Query Explanation needed

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;

 

 

 

Super Contributor
Posts: 251

Re: Query Explanation needed

Oh **bleep** - I misremembered. I apologise.

Super Contributor
Posts: 251

Re: Query Explanation needed

I was trying to say d__n, by the way, in case you thought I was aiming for something stronger. 

Super User
Super User
Posts: 7,432

Re: Query Explanation needed

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;
Ask a Question
Discussion stats
  • 13 replies
  • 131 views
  • 1 like
  • 4 in conversation