topic if not, else if proc sql in SAS Programming

if not, else if proc sql

sasprogramming — Mon, 09 Dec 2019 05:42:15 GMT

I have the current sas code that works fine:

DATA td.data_new
set td.data;
if not missing(A) then Points = 250;
else if B >= 10 and C= 0 then Points = 230;
else if D <6 and E >60 then Points = 150;
run;

I want to convert this to some equivalent SQL code, so that I can use it in a proc sql statement. Any help would be appreciated, thanks.

Re: if not, else if proc sql

Jagadishkatam — Mon, 09 Dec 2019 06:03:41 GMT

please try

proc sql;
create table td.data_new as select a,b,c,d,e, case when a ne . then 250 else when b>=10 and c=0 then 230, else when d<6 and e>60 then 150 else . end as points from td.data;
quit;

Re: if not, else if proc sql

sasprogramming — Mon, 09 Dec 2019 22:23:44 GMT

Thank you but I am receiving this error?

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,

GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Re: if not, else if proc sql

ed_sas_member — Wed, 11 Dec 2019 20:05:33 GMT

Hi @sasprogramming

You need to mention "ELSE" only once at the end and remove commas.

proc sql;
	create table data_new as
	select a,b,c,d,e,
	case when a ne . then 250
		 when b>=10 and c=0 then 230
		 when d<6 and e>60 then 150
		 else . end as points
	from data;
quit;

Best,