I have the following code and error message.
Proc Sql;
Create Table CR_Temp as
SELECT
Input(YYYY,4.) As Year
,Vgroup||'A'||st||vtype||vdesc1||vdesc2||sens As ArtificialKey
,Input(CommodityCode,5.) As CommodityCode
,vtype||vdesc1||vdesc2 As GroupType
,Vtype
,Vdesc1
,Vdesc2
,StateName
,FIPS
,VariableDescription
,unit_desc
,amount
FROM FarmInc.Public_data_view
Where Year between '2007' and '2013' and MM = '00' and
vgroup = 'CR' and vdesc2 = 'VA' /* All Cash receipt values */
Order by Year,CommodityCode,FIPS;
Quit;
ERROR: The following columns were not found in the contributing tables: Year.
Why doesn't the procedure accept "Year" following "As" and use it in the WHERE and Order by clauses? If I use "YYYY" following "As", it works.
'Year' is created as numeric, so in your WHERE clause, it should be:
Where Calculated Year between 2007 and 2013
Calculated Year?
I tried using Calculated Year in the WHERE and Order by clauses and got the error message and Note:
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.
'Year' is created as numeric, so in your WHERE clause, it should be:
Where Calculated Year between 2007 and 2013
Yes, I just figured that out. I converted YYYY to a numeric format and then used character values in the WHERE clause.
Thanks for the help!
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 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.