topic Re: Timestamp question in SAS Programming

Syntax error in code

deenap29 — Tue, 02 Oct 2018 15:49:33 GMT

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;

Re: Syntax error in code

Kurt_Bremser — Tue, 02 Oct 2018 16:04:48 GMT

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.

Re: Syntax error in code

FreelanceReinh — Tue, 02 Oct 2018 16:05:57 GMT

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.

Re: Syntax error in code

Kurt_Bremser — Tue, 02 Oct 2018 16:07:07 GMT

@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.

Re: Syntax error in code

deenap29 — Tue, 02 Oct 2018 18:45:43 GMT

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...

Re: Syntax error in code

Kurt_Bremser — Tue, 02 Oct 2018 19:02:32 GMT

Maxim 3: know your data.

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

Re: Syntax error in code

FreelanceReinh — Tue, 02 Oct 2018 19:19:22 GMT

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?

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 14:18:22 GMT

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.

Re: Syntax error in code

FreelanceReinh — Wed, 03 Oct 2018 15:05:28 GMT

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.

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 15:16:16 GMT

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

Type: Char

Len: 3

Format:$3.

Informat:$3.

Label:LIAB_PCT

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 15:36:25 GMT

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.

Re: Syntax error in code

FreelanceReinh — Wed, 03 Oct 2018 16:01:36 GMT

@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.

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 17:38:06 GMT

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.

Re: Syntax error in code

FreelanceReinh — Wed, 03 Oct 2018 18:32:04 GMT

@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.)

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 18:55:05 GMT

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?

Re: Syntax error in code

Tom — Wed, 03 Oct 2018 19:05:42 GMT

@deenap29 wrote:

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?

What are you trying to do?

If you just want to use COALESCE() to report whichever non-missing value is first among your list of fields then convert everything to character strings.

left(put(numvar,best32.))

But if you need to actually preform arithmetic (or find min, max, mean, sum, etc) with those values then you will need to convert the characters to numbers. How to do that really depends on what is meaning of the a letter followed by digits. If they really are in the form A9999 then perhaps you can just use SUBSTR() to skip the first letter.

input(substr(charvar,2),32.)

You should also investigate what about your earlier processes is resulting in you having mixed data types for variables that you consider as capturing equivalent information. Are you using PROC IMPORT or some other guessing procedure to create your datasets? Perhaps you could convert those steps to data steps so you can explicitly define the data types for the variables.

Re: Syntax error in code

deenap29 — Wed, 03 Oct 2018 19:27:37 GMT

It worked!!!!

Thank you again very much for your patience and help with this! I appreciate it!!

Error 79-322: Expecting a THEN

deenap29 — Tue, 05 Feb 2019 19:38:55 GMT

Error 79-322: Expecting a THEN

For the case statement below, I keep getting the error in red. Not sure where a then is missing? Please help!!!

70 /* Include Owner? */
71 (CASE
72 WHEN THEN 'N'
___
79
73 WHEN THEN 'N'
___
79
ERROR 79-322: Expecting a THEN.

74 WHEN '949 IRVING' = t1.current_owner_fn THEN 'N'
75 WHEN 'CLAIMS INTAKE - LIBERTY MUTUAL (CL)' = t1.current_owner_fn THEN 'N'
76 WHEN 'CLAIMS INTAKE - LIBERTY MUTUAL (CL) SUPPORT' = t1.current_owner_fn THEN 'N'
77 WHEN 'GENERAL AM INBOX - LIBERTY MUTUAL (CL)' = t1.current_owner_fn THEN 'N'
78 WHEN 'LIBERTY MUTUAL COMMERCIAL SUBRO' = t1.current_owner_fn THEN 'N'
79 WHEN 'LIBERTY MUTUAL INS - COMMERCIAL CLAIMS' = t1.current_owner_fn THEN 'N'
80 ELSE 'Y'
81 END) FORMAT=$CHAR43. LABEL="Include Owner?" AS 'Include Owner?'n,

Case statement:

CASE
WHEN THEN 'N'
WHEN THEN 'N'
WHEN '949 IRVING' = t1.current_owner_fn THEN 'N'
WHEN 'CLAIMS INTAKE - LIBERTY MUTUAL (CL)' = t1.current_owner_fn THEN 'N'
WHEN 'CLAIMS INTAKE - LIBERTY MUTUAL (CL) SUPPORT' = t1.current_owner_fn THEN 'N'
WHEN 'GENERAL AM INBOX - LIBERTY MUTUAL (CL)' = t1.current_owner_fn THEN 'N'
WHEN 'LIBERTY MUTUAL COMMERCIAL SUBRO' = t1.current_owner_fn THEN 'N'
WHEN 'LIBERTY MUTUAL INS - COMMERCIAL CLAIMS' = t1.current_owner_fn THEN 'N'
ELSE 'Y'
END

Re: Error 79-322: Expecting a THEN

Tom — Tue, 05 Feb 2019 19:49:01 GMT

Your first two rows do not have any condition specified. I assume that the parser is considering the text THEN as the name of a variable to evaluate as the condition and that is why it is complaining that there is no THEN.

Timestamp question

deenap29 — Fri, 08 Feb 2019 15:56:49 GMT

Thank you! I have a DATEPART question for you. It worked in the first part of the formula but not in the second after THEN. Im basically trying to get rid of the timestamp so the valuation month and year populate instead of being blanks.

CASE
WHEN t1.'ANS RCVD'n IS MISSING AND Year(DATEPART(t1.'ANS DUE'n)) < 2013 THEN INTNX('month',t1.CLOSED,0,'E')
WHEN t1.'ANS RCVD'n IS MISSING AND Year(DATEPART(t1.'ANS DUE'n)) >= 2013 THEN INTNX('month',t1.'ANS DUE'n ,0,'E')
WHEN Year(DATEPART(t1.'ANS RCVD'n)) < 2013 AND Year(DATEPART(t1.'ANS DUE'n)) < 2013 THEN INTNX('month',t1.CLOSED,0,'E')
WHEN Year(DATEPART(t1.'ANS RCVD'n)) < 2013 AND Year(DATEPART(t1.'ANS DUE'n)) >= 2013 THEN INTNX('month',t1.'ANS DUE'n,0,'E')
ELSE INTNX('month',t1.'ANS RCVD'n,0,'E')

END