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