topic Re: SQL in SAS Programming

SQL

SASPhile — Tue, 27 Nov 2012 18:27:04 GMT

for the following I'm getting an error:

proc sql;
create table outdata.canada_master
as
                 select
                        a.ACCT_NO
                        ,max(case when a.ACCT_FIRST_NM ne '' then a.ACCT_FIRST_NM end) as ACCT_FIRST_NM
                        ,max(case when a.ACCT_MID_NM ne '' then a.ACCT_MID_NM end ) as ACCT_MID_NM
                        ,max(case when a.ACCT_LAST_NM ne '' then a.ACCT_LAST_NM end) as ACCT_LAST_NM
                        ,max(case when b.CUST_NAT_ID_CD ne ' ' then catx('-',d.iso_cntry_cd,b.CUST_NAT_ID_CD) else ' ' end) as CUST_NAT_ID
                        ,max(case when 2012-input(substr(a.BIRTH_DT,7,4),4.) gt 17 and input(substr(a.BIRTH_DT,7,4),4.) gt 1900 end ) as BIRTH_DT
      ,max(a.CUST_ID) as a_amex
      ,max(a.MKT_ID_cd) as MKT_ID_cd

from outdata1.CRPS311 a left join outdata1.crps311_canada b on a.acct_no=b.acct_no
left join outdata1.isocodes d on b.mkt_id_cd=d.mkt_id_cd
group by a.ACCT_NO;
quit;

101        create table outdata.canada_master
102        as
103                         select
104                                a.ACCT_NO
105                                ,max(case when a.ACCT_FIRST_NM ne '' then a.ACCT_FIRST_NM end) as ACCT_FIRST_NM
106                                ,max(case when a.ACCT_MID_NM ne '' then a.ACCT_MID_NM end ) as ACCT_MID_NM
107                                ,max(case when a.ACCT_LAST_NM ne '' then a.ACCT_LAST_NM end) as ACCT_LAST_NM
108                                ,max(case when b.CUST_NAT_ID_CD ne ' ' then catx('-',d.iso_cntry_cd,b.CUST_NAT_ID_CD) else ' '
108      ! end) as CUST_NAT_ID
109                                ,max(case when 2012-input(substr(a.BIRTH_DT,7,4),4.) gt 17 and input(substr(a.BIRTH_DT,7,4),4.)
109      ! gt 1900 end ) as BIRTH_DT
                   ___
                   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, NOT, OR, THEN, ^, ^=, |, ||, ~, ~=.

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

Re: SQL

art297 — Tue, 27 Nov 2012 18:49:21 GMT

What happens when you remove change the 4. to 4 (without the period) in your last line? i.e.,

change: input(substr(a.BIRTH_DT,7,4),4.)

to: input(substr(a.BIRTH_DT,7,4),4)

Re: SQL

SASKiwi — Tue, 27 Nov 2012 19:04:11 GMT

It looks like your CASE statement is incomplete. Where the error underlines your code I would expect the word THEN, but what follows I have no idea because you haven't explained what the CASE statement is trying to do. Don't forget you usually have an ELSE clause in a CASE statement as well.

Re: SQL

SASPhile — Wed, 28 Nov 2012 02:10:26 GMT

But a period is required when we change the format right?

Re: SQL

art297 — Wed, 28 Nov 2012 02:14:06 GMT

Yes, I misread your code and will delete my previous post. Change that! While it my advice was wrong, I don't want to delete the posts that responded to it. However, since I'm looking at your code again, what is that line supposed to be doing? All of your others are if then statements except for that one. It has a case condition, but does it have an assignment?

Re: SQL

Tom — Wed, 28 Nov 2012 02:49:42 GMT

The last line looks a little hard to parse. Try adding more () to let SAS know how to group conditionals and functions and more white space to help the humans scan it.

Also I prefer to use this syntax for CASE statements: CASE WHEN (condition) THEN value1 ELSE value2 END

,max(

case

when (

((2012-input(substr(a.BIRTH_DT,7,4),4.)) gt 17 )

and (input(substr(a.BIRTH_DT,7,4),4.) gt 1900)

)

THEN WHAT????? <- What value do you want to use for BIRTH_DT for this WHEN clause?

ELSE WHAT???? <- What value do you want to use when condition is not met?

end

) as BIRTH_DT

Re: SQL

SASPhile — Wed, 28 Nov 2012 04:28:34 GMT

there are some values like 0001 in year ,so this is defensive coding to avoid such years and also we need ppl who are older than 17.

Re: SQL

LinusH — Wed, 28 Nov 2012 11:50:40 GMT

Still not getting the purpose of these queries. Why all the max()-logic?

Re: SQL

SASPhile — Wed, 28 Nov 2012 15:21:02 GMT

Well.. if there are two names for one account and the two names vary by a letter say John and Johnk we will take Johnk