BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

'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

4 REPLIES 4
cjmcgath_verizon_net
Calcite | Level 5

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
Onyx | Level 15

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

Where Calculated Year between 2007 and 2013

cjmcgath_verizon_net
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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