Im trying to clean the data by adding 1 year to Date_Received if Incident_Date > Date Received
proc sql;
create table sq.Claims_Cleaned as
Select Distinct
Claim_Number,
(Case
when Incident_Date > Date_Received then INTNX(YEAR,Date_Received,1,S) else Date_Received
End) as Date_Received,
Incident_Date,
(Case
when Airport_Code is null then "Unknown" else Airport_Code
end) as Airport_Code,
Airport_Name,
(Case
when Claim_Type is null then "Unknown"
when Claim_Type contains "/" then scan(Claim_Type,1,"/")
else Claim_Type
end) as Claim_Type,
(Case
when Claim_Site is null then "Unknown" else Claim_Site
end) as Claim_Site,
Close_Amount,
(Case
when Disposition is null then "Unknown" else Disposition
end) as Disposition,
StateName,
State,
County,
City
From SQ.ClaimsRaw;
Quit;
However, I hit an error saying the column year and S are not found in the table.
May I know why the error happens instead of adding 1 year to Date_Received?
The Date_Received is in format = date9.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 proc sql;
74 create table sq.Claims_Cleaned as
75 Select Distinct
76 Claim_Number,
77 (Case
78 when Incident_Date > Date_Received then INTNX(YEAR,Date_Received,1,S) else Date_Received
79 End) as Date_Received,
80 Incident_Date,
81 (Case
82 when Airport_Code is null then "Unknown" else Airport_Code
83 end) as Airport_Code,
84 Airport_Name,
85 (Case
86 when Claim_Type is null then "Unknown"
87 when Claim_Type contains "/" then scan(Claim_Type,1,"/")
88 else Claim_Type
89 end) as Claim_Type,
90 (Case
91 when Claim_Site is null then "Unknown" else Claim_Site
92 end) as Claim_Site,
93 Close_Amount,
94 (Case
95 when Disposition is null then "Unknown" else Disposition
96 end) as Disposition,
97 StateName,
98 State,
99 County,
100 City
101 From SQ.ClaimsRaw;
ERROR: The following columns were not found in the contributing tables: S, YEAR.
Hi:
My suggestion is that you review the INTNX documentation. One of the requirements for some of the arguments is that they need to be quoted. Here's the web site: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p10v3sa3i4kfxfn1sovhi5xzxh8... and here are some examples from that page:
Hope this helps,
Cynthia
You are asking for those variables in your INTNX() function call.
INTNX(YEAR,Date_Received,1,S)
The fist and last arguments to INTNX() need to be character strings.
Try this instead:
INTNX('YEAR',Date_Received,1,'S')
You probably will also want to attach a format to that new variable so it prints in a way that humans will recognize as a date.
as Date_Received format=yymmdd10. ,
The macro processor is a completely different language than that used in SAS code.
In SAS code a stream of letters like YEAR or S is used to indicate a variable name. A literal text string has to be enclosed in quotes so SAS knows you don't mean a variable name. A format reference has to include a period so SAS knows you didn't mean a variable name.
In your macro code example the %SYSFUNC() macro processor function is responsible for taking the text in your code and passing it to the function call as the right type of value. To the macro processor everything is text. If you add quotes they are considered part of the text. So if you tried to use this in macro code:
%sysfunc(intnx('day',%sysfunc(today()),-1,'s'))
The macro processor would pass the quotes to the INTNX() function and it would complain that 'day' is not a valid interval since it starts with a quote and not a letter.
It would be like trying to do this in SAS code
intnx("'day'",today(),-1,"'s'")
PS There is no need to use INTNX() when the interval you are moving is in the units that the values are stored in. So no need to use the DAY interval for DATE value or the SECOND interval with TIME or DATETIME values. Note you do need to use the DTDAY interval to adjust DATETIME values by day intervals since datatime values are stored in seconds not in days.
If you want to subtract one day from a date just subtract 1 from the date.
today()-1
Or in macro code
%eval( %sysfunc(today()) -1 )
So this SQL code
Case when Airport_Code is null then "Unknown" else Airport_Code end as Airport_Code
is just a complex way to write
coalesce(Airport_Code,"Unknown") as Airport_Code
Note that in SQL code COALESCE() can be used for either numeric or character values.
But if want to use it in normal SAS code, like a DATA STEP, then the COALESCE() function only works with numeric values. For character values you need to use the COALESCEC() function.
Let me Google it for you:
Query: "SAS 9.4 COALESCEC() function"
Result (the first from the top): https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/ds2ref/n0crpo0xd76wb3n1poba9wmu1a6q.htm
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 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: