DATA Step, Macro, Functions and more

Case statement

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Case statement

Hi 

 

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!


Accepted Solutions
Solution
‎06-28-2016 01:39 PM
Respected Advisor
Posts: 4,927

Re: Case statement

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.

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,927

Re: Case statement

[ Edited ]

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) 

PG
Contributor
Posts: 26

Re: Case statement

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?

Solution
‎06-28-2016 01:39 PM
Respected Advisor
Posts: 4,927

Re: Case statement

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.

PG
Super User
Super User
Posts: 7,060

Re: Case statement

[ Edited ]

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;

   

Respected Advisor
Posts: 4,927

Re: Case statement


Tom wrote:

Why not learn SAS instead of SQL?

 

Because only SAS understands SAS? Smiley Happy

 

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

 

 

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 345 views
  • 1 like
  • 3 in conversation