topic Re: Case statement in SAS Programming

Case statement

itshere — Tue, 28 Jun 2016 15:57:23 GMT

Im familiar to do an IF statement in Excel but not in SAS ( yet)

Im using proc sql and want to create a new table with the results.

I have two tables and want to create a third one that says if the account number matches from table 1 with table 2 then add an another column in table 1 and say YES otherwise no.

Finish this...

Create table test1.new as

select A.* (table1)

----case statement--- ( if account number column matches from table 1) then create new column and say YEs otherwise No,

Thanks!

Re: Case statement

PGStats — Tue, 28 Jun 2016 16:43:55 GMT

SQL provides you with much more flexibility than Excel formulas. Once you master SQL basics, you will not want to go back. I suspect that you would want to do a lot more in a single query :

proc sql;
create table test1.new as
select 
	*, 
	case 
		when accountNb in (select accountNb from table2) then "YES"
		else "NO" end as inTable2
from table1;
quit;

(untested)

Re: Case statement

Tom — Tue, 28 Jun 2016 16:58:53 GMT

Why not learn SAS instead of SQL?

To combine two tables use the MERGE statement. If you have other variables in TABLE2 that you do not want to carry over into the new table then use a KEEP= or DROP= dataset option. Personally I find it easier to use 1/0 numeric variables than 'YES'/'NO' character variables. Below is code to make both.

data want ;
   merge table1 (in=in1) table2(in=in2 keep=id);
   by id;
   if in1;
   new_numeric=in2;
   if in2 then new_character='YES';
   else new_character='NO ';
run;

Re: Case statement

PGStats — Tue, 28 Jun 2016 17:06:50 GMT

@Tom wrote:

Why not learn SAS instead of SQL?

Because only SAS understands SAS?

Seriously: if you are going invest in SAS, learn both! You'll need both to tackle DS2 later.

Re: Case statement

itshere — Tue, 28 Jun 2016 17:09:46 GMT

Thank you but I get a Syntax Error

proc sql;
create table test.intscrub as
select A.*, case when A.account_number in (select B.Acc_Num from test.scrub B) then "YES" else "No" end as test.scrub B
from test.intscrub A
;quit;

any suggestions?

Re: Case statement

PGStats — Tue, 28 Jun 2016 17:23:31 GMT

It makes no sense to say "as test.scrub B". Get rid of the A's and B's in your query, they aren't needed. Read about aliases in the SAS/SQL doc.

proc sql;
create table test.intscrub_1 as
select *, case when account_number in (select Acc_Num from test.scrub) then "YES" else "No" end as scrub
from test.intscrub;
quit;

Do not overwrite your input table until you have fully validated your query.