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.
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)
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.
But a period is required when we change the format right?
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?
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
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.
Still not getting the purpose of these queries. Why all the max()-logic?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.