topic INTNX function on cleaning data in SAS Studio

INTNX function on cleaning data

ChrisWoo — Mon, 15 May 2023 18:13:00 GMT

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.

Re: INTNX function on cleaning data

Cynthia_sas — Mon, 15 May 2023 18:18:56 GMT

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

Hope this helps,

Cynthia

Re: INTNX function on cleaning data

Tom — Mon, 15 May 2023 18:20:54 GMT

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

Re: INTNX function on cleaning data

Reeza — Mon, 15 May 2023 19:55:13 GMT

FYI - you should look into the COALESCE/COALESCEC function as well. for your Claim Site, Disposition, airport_code variables.

Re: INTNX function on cleaning data

ChrisWoo — Tue, 16 May 2023 01:11:17 GMT

But I remember i used it in macro statements %sysfunc(intnx(day,%sysfunc(today()),-1,s))

I didnt put quote and its working fine

Re: INTNX function on cleaning data

ChrisWoo — Tue, 16 May 2023 01:15:05 GMT

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

Re: INTNX function on cleaning data

Tom — Tue, 16 May 2023 03:03:55 GMT

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 )

Re: INTNX function on cleaning data

Tom — Tue, 16 May 2023 03:10:25 GMT

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.

Re: INTNX function on cleaning data

yabwon — Tue, 16 May 2023 09:00:50 GMT

Use Maxim 1.

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