Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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.


Accepted Solutions
Solution
‎04-24-2015 11:38 AM
Respected Advisor
Posts: 3,156

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

Posted in reply to cjmcgath_verizon_net

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

Where Calculated Year between 2007 and 2013

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

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

Posted in reply to cjmcgath_verizon_net

Calculated Year?

Occasional Contributor
Posts: 14

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

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.

Solution
‎04-24-2015 11:38 AM
Respected Advisor
Posts: 3,156

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

Posted in reply to cjmcgath_verizon_net

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

Where Calculated Year between 2007 and 2013

Occasional Contributor
Posts: 14

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

Posted in reply to cjmcgath_verizon_net

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 257 views
  • 1 like
  • 2 in conversation