topic Re: Why doesn't Proc SQL use variable name following "As"? in SAS Procedures

Why doesn't Proc SQL use variable name following "As"?

cjmcgath_verizon_net — Fri, 24 Apr 2015 15:20:23 GMT

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.

Haikuo — Fri, 24 Apr 2015 15:26:23 GMT

Calculated Year?

cjmcgath_verizon_net — Fri, 24 Apr 2015 15:33:55 GMT

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.

Haikuo — Fri, 24 Apr 2015 15:38:35 GMT

'Year' is created as numeric, so in your WHERE clause, it should be:

Where Calculated Year between 2007 and 2013

cjmcgath_verizon_net — Fri, 24 Apr 2015 15:39:41 GMT

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!