BookmarkSubscribeRSS Feed
srdhoble
Calcite | Level 5

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;

 

13 REPLIES 13
Reeza
Super User

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? 

srdhoble
Calcite | Level 5

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

srdhoble
Calcite | Level 5

Also,Can you please help me explain below two commands

Coalesce(B.ICD9, ' ')

Coalesce(B.SMM,0)=0

Reeza
Super User

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...

 

 

LaurieF
Barite | Level 11

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.

 

Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

LaurieF
Barite | Level 11

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.

LaurieF
Barite | Level 11
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!
Reeza
Super User

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;

 

 

 

LaurieF
Barite | Level 11

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

LaurieF
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1286 views
  • 1 like
  • 4 in conversation