topic Re: Query Explanation needed in SAS Programming

Query Explanation needed

srdhoble — Wed, 01 Feb 2017 01:48:14 GMT

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;

Re: Query Explanation needed

Reeza — Wed, 01 Feb 2017 01:55:39 GMT

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?

Re: Query Explanation needed

srdhoble — Wed, 01 Feb 2017 01:59:41 GMT

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

Re: Query Explanation needed

LaurieF — Wed, 01 Feb 2017 02:05:16 GMT

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.

Re: Query Explanation needed

srdhoble — Wed, 01 Feb 2017 02:02:12 GMT

Also,Can you please help me explain below two commands

Coalesce(B.ICD9, ' ')

Coalesce(B.SMM,0)=0

Re: Query Explanation needed

Reeza — Wed, 01 Feb 2017 02:14:21 GMT

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#p1vjttz6nuankzn1gh4z3wgcu0bf.htm

Re: Query Explanation needed

LaurieF — Wed, 01 Feb 2017 02:18:05 GMT

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.

Re: Query Explanation needed

Reeza — Wed, 01 Feb 2017 02:37:44 GMT

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

Re: Query Explanation needed

LaurieF — Wed, 01 Feb 2017 02:42:09 GMT

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!

Re: Query Explanation needed

Reeza — Wed, 01 Feb 2017 03:02:44 GMT

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;

Re: Query Explanation needed

LaurieF — Wed, 01 Feb 2017 04:21:51 GMT

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

Re: Query Explanation needed

LaurieF — Wed, 01 Feb 2017 04:24:58 GMT

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

Re: Query Explanation needed

RW9 — Wed, 01 Feb 2017 09:31:19 GMT

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.

Re: Query Explanation needed

RW9 — Wed, 01 Feb 2017 09:41:06 GMT

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;