Syntax error in code

[deenap29]

Below is the code im working on and getting a syntax error (the part im getting the error for is in red below). I have attached a picture of the error for reference. Any help would be appreciated!! 

PROC SQL;
CREATE TABLE WORK.ADDL_FIELDS_JOINS AS
SELECT t1.'Evnt Id'n LABEL='',
t1.'Exposure Number'n,
t1.'System Code'n,
t1.'Typ Cde'n,
t1.'Typ Nme Lvl 2'n,
t1.Operation,
t1.'Typ Nme'n,
t1.Market,
t1.'Central Region Overwrite'n AS Region,
t1.'Legacy Group'n,
t1.'Stt Cde'n,
t1.'HR Region'n,
(case
when t1.'Loss Incr Amt'n IS MISSING then 0
else t1.'Loss Incr Amt'n
end) LABEL="Loss Incr Amt" AS 'Loss Incr Amt'n,
t1.'Loss Incurred?'n,
t1.'Valtn Dte'n,
t1.'Valtn Yr'n,
t1.'Valtn Month'n,
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)
) AS 'Insured % of Fault'n,
(coalesce((case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) is not missing
then 100 - (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT))
end),t4.OFFSET_PCT)) AS 'Claimant % of Fault'n,
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) AS 'Insured Fault Rating Name'n,
(case when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) = 'At Fault' then 'No Offset'

when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) in ('Not at fault') then 'Full Offset'

when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) in ('Partially at Fault') then 'Partial Offset'

else 'Blank'
end) AS 'Offset Eligibility'n,
(Case when (case when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) = 'At Fault' then 'No Offset'

when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) in ('Not at fault') then 'Full Offset'

when
(case when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 0 then 'Not at fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) > 0 and
(coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) < 100 then 'Partially at Fault'
when (coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)) = 100 then 'At Fault'
end) in ('Partially at Fault') then 'Partial Offset'

else 'Blank'
end) = 'Blank'
then 'Blank'
else 'Field Complete'
end) AS 'Offset Eligibility Group'n,
(case when t1.Customer in
('United Parcel','United Parcel Service','United Parcel Service Inc','United Parcel Service, Inc.',
'United Parcel Servive', 'UNITED PARCEL SERVICE','UNITED PARCEL SERVICE INC')
then 'Y'
else 'N' end) AS 'UPS?'n,
(case
when t1.'System Code'n = 'CC' then t2.'Loss Cause'n
when t1.'System Code'n = 'Safeco' then Strip(t4.'CVO_CAPTION2'n) || Strip(t4.'CVO_CAPTION3'n)
else t1.'Case Desc'n
end) AS 'Loss Cause'n,

(PROPCASE(t1.Manager)) AS Manager,
(PROPCASE(t1.Owner)) AS Owner,
t1.'Hndlg Off Cde'n,
t4.'CVO_CASE_DESC'n,
t4.'CVO_AUT_LS_TYP'n,
t4.'CVO_AUT_COLL_CD'n,
t4.'CVO_CAPTION1'n,
t4.'CVO_CAPTION2'n,
t4.'CVO_CAPTION3'n,
t4.'CVO_CAPTION4'n,
t1.Closed_Date
FROM WORK.QUERY_FOR_ALL_CLOSED_SAS7BDAT1 t1
LEFT JOIN WORK.CC t2 ON put(t1.'Exposure Number'n, $50.) = put(t2.'Exposure Number'n, $50.).
LEFT JOIN WORK.CVO t4 ON put(t1.'Exposure Number'n, $50.) = put(t4.'Exposure Number'n, $50.).
LEFT JOIN newlib.VIA_OFFSETS t3 ON put(t1.'Exposure Number'n, $50.) = put(t3.'Exposure Number'n, $50.);

QUIT;

[Kurt_Bremser]

Please post the whole log of a failed step in a window opened with the {i} button.

Code is best posted in a window opened with the "little running man" button. Both methods preserve the formatiing.

I positively hope that your code does not look as ugly as what you posted here.

And I STRONGLY recommend you get rid of those ugly name literals three days before yesterday, and refrain for all future from creating new variables with them.

[FreelanceReinh]

Hello @deenap29 and welcome to the SAS Support Communities!

Maybe it's quite simple: Remove the periods at the end of the first two lines printed in red.

[Kurt_Bremser]

---

@FreelanceReinh wrote:

Hello @deenap29 and welcome to the SAS Support Communities!

Maybe it's quite simple: Remove the periods at the end of the first two lines printed in red.

---

Good catch.

[deenap29]

Hello - thank you for replying, appreciate it! I tried removing the commas but then i get the coalesce function error which states that it requires its arguments to be of the same data type for all the joins that were listed in red. Not sure if you have any insight on that...

[Kurt_Bremser]

Maxim 3: know your data.

Columns used as arguments in a coalesce() function have to be of the same type.

[FreelanceReinh]

This shouldn't be too difficult either because 31 of the 32 occurrences of coalesce are identical expressions:

coalesce(t2.FAULT,t3.'Insured Fault %'n,t4.LIAB_PCT)

So, one or two of the three arguments are character variables while the rest are numeric. Apparently you want the result to be numeric. Hence, you should convert the character argument(s) to numeric values. This can be done with the INPUT function if the character values can be interpreted as numeric values (e.g. "1.5" or "1.5%").

Which are the character variables and what kind of values do they contain?

[deenap29]

Hello - I looked at the four tables mentioned in the join (exposure number) and this is what I found...

work.CC - numeric (10-11 char long)

work.CVO - numeric and one alphabet (A) (12 to 13 char long)

work.query for all closed - numeric (10-11 char long)

new lib.via offset - numeric and varies in size 

as for the fields...

FAULT - '.' and numbers and decimals 

INSURED FAULT % - '.' and decimals 

LIAB PCT - blank and numbers 

Im not entirely sure how to proceed with the input function since all of these vary. Please let me know if you have any insight. thank you. 

[FreelanceReinh]

Thanks @deenap29 for the details. So, indications are that t4.LIAB_PCT is the character variable among the three arguments in question: The blank is the typical character missing value (whereas a single period denotes a numeric missing value) and t4 is the alias of work.CVO, which you say contains one character variable unlike the other datasets. PROC CONTENTS output would have shown definitive information about the variable types and lengths, for example:

proc contents data=work.cvo;
run;

What does the output of the above step tell about LIAB_PCT?

Based on the preliminary assumptions I would change the 31 identical COALESCE expressions (using Search and Replace in the editor) as follows:

coalesce(t2.FAULT,t3.'Insured Fault %'n,input(t4.liab_pct, 16.))

This converts the "numeric" values contained in character variable LIAB_PCT (assuming a maximum length <=16) to real numeric values.

[deenap29]

Hi again - the output of the LIAB_PCT states the following...

Type: Char

Len: 3

Format:$3.

Informat:$3.

Label:LIAB_PCT 

[deenap29]

I tried using the input function you mentioned just now and received syntax errors. Could this be due to parentheses not matching up? 

I attached a snip of what I'm seeing. 

[FreelanceReinh]

---

@deenap29 wrote:

I tried using the input function you mentioned just now and received syntax errors. Could this be due to parentheses not matching up? 

---

No, the error messages rather indicate that the slanted single quotes around "Insured Fault %" are the culprit. Note that they differ from the (correct) single quotes around "Not at fault" etc. So, I'd try another Search/Replace to correct them.

[deenap29]

Hello - Wanted to say thank you first for your help thus far. It has made me understand my errors and also the approach to fix them. Hopefully this is the last question but the input statement and fixing the quotations worked. I still have one coalesce error and could that be due to the types not matching? i can take a look at all the tables/fields again to see if theres something i missed. Attached a screenshot for reference. 

[FreelanceReinh · Accepted solution]

---

@deenap29 wrote:

I still have one coalesce error and could that be due to the types not matching?

---

You're welcome. Yes, most likely variable OFFSET_PCT in dataset work.CVO is a character variable (please check PROC CONTENTS output). If so, the same conversion that we applied to LIAB_PCT should resolve the issue: input(t4.offset_pct, 16.)

[deenap29]

I was looking at the tables/fields again and realized that 

t1 work.query for all closed has numeric and alphabet then numbers at the very end (starting with Y). Should I follow the same logic as before as you mentioned above?