BookmarkSubscribeRSS Feed
Obsidian | Level 7

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
			when Incident_Date > Date_Received then INTNX(YEAR,Date_Received,1,S) else Date_Received
			End) as Date_Received,
			when Airport_Code is null then "Unknown" else Airport_Code 
			end) as Airport_Code,
			when Claim_Type is null then "Unknown"
			when Claim_Type contains "/" then scan(Claim_Type,1,"/")
			else Claim_Type 
			end) as Claim_Type,			
			when Claim_Site is null then "Unknown" else Claim_Site 
			end) as Claim_Site,
			when Disposition is null then "Unknown" else Disposition 
			end) as Disposition,
	From SQ.ClaimsRaw;

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.

 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.


  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: and here are some examples from that page:


Hope this helps,


Super User Tom
Super User

You are asking for those variables in your INTNX() function call.


The fist and last arguments to INTNX() need to be character strings.


Try this instead:


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. ,
Obsidian | Level 7
But I remember i used it in macro statements %sysfunc(intnx(day,%sysfunc(today()),-1,s))

I didnt put quote and its working fine
Super User Tom
Super User

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:


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


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.


Or in macro code

%eval( %sysfunc(today()) -1 )
Super User
FYI - you should look into the COALESCE/COALESCEC function as well. for your Claim Site, Disposition, airport_code variables.
Obsidian | Level 7
Any suggested code? I not sure how to use COALESCEC() to replace missing value

Therefore, i chose to use case/when/then to list my condition and replace the missing value to “unknown”.
Super User Tom
Super User

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.

Onyx | Level 15

Use Maxim 1.


Let me Google it for you:

Query: "SAS 9.4 COALESCEC() function"

Result (the first from the top):




Polish SAS Users Group: and

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5 in conversation