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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.