topic Re: where condition if using multiple datasets in proc sql join in New SAS User

where condition if using multiple datasets in proc sql join

vraj1 — Fri, 05 Oct 2018 12:19:23 GMT

Can we use case when or where condition if we join multiple datasets using proc sql.

proc sql;
    create table x_01 as
	select a.*, 
		   b.TDC_RAW as DTC_A,
		   c.TDC_RAW as DTC_B,
 		 case when NDC_RAW ne ""  d.NDC_RAW as DC_A,
		   e.NDC_RAW as DC_B
	from rs a left join _dat b on a.ent=b.ect
	left join dat_b c on a.ent=c.ect
	left join dat d on a.ent=d.ect
	left join dat_b e on a.ent=e.ect
    
quit;

in the above code i am using case just to filter non missing from one of the dataset but it gives error

-
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,
<>, =, >, >=, AND, EQ, EQT, GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR,
THEN, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Any help if we can use where/case condition while joining multiple datasets

Re: where condition if using multiple datasets in proc sql join

RW9 — Fri, 05 Oct 2018 12:23:58 GMT

Yes, you can use case, but you have to use the right syntax. Either:
case <expression> when <result> then <value> when <result> then <value> else <value> end

or case when <expression> then <value>...

Something like:

proc sql;
    create table x_01 as
	select a.*, 
		   b.TDC_RAW as DTC_A,
		   c.TDC_RAW as DTC_B,
 		 case when d.NDC_RAW ne "" then d.NDC_RAW else "" end as DC_A,
		   e.NDC_RAW as DC_B
	from rs a left join _dat b on a.ent=b.ect
	left join dat_b c on a.ent=c.ect
	left join dat d on a.ent=d.ect
	left join dat_b e on a.ent=e.ect
    
quit;

Note, I put an alias in for ndc_raw here also, you need to be clear which ndc_raw value you are using otherwise you will get an error stating it can't decide which one.

Re: where condition if using multiple datasets in proc sql join

Kurt_Bremser — Fri, 05 Oct 2018 12:24:21 GMT

Correct "case" code in SQL looks like this:

case
  when d.NDC_RAW ne ""  
  then d.NDC_RAW
  else ""
end as DC_A,

you can have multiple when-then pairs.