Help using Base SAS procedures

Change in SQL from 9.1 to 9.3

Reply
New Contributor
Posts: 4

Change in SQL from 9.1 to 9.3

I there. This is part of an auto process program I created. I recently upgraded from 9.1 to 9.3 and I'm having trouble with some queries that used to work just fine. See below for the query and then below the query I have the Error that now occurs in 9.3.

proc sql;

    create table get_load as

SELECT

    t.DEAL_OPTION_ID as VHET_SUBDEAL_ID,

    t.CUSTOMER,

    t.CLOSEDDATE,

    t.PRODUCT_ID,

    t.PRODUCT_ATTRIBUTE as COMPONENT_NAME,

    t.NUM_VALUE,

    t.BUNDLE_OPTION as BUNDLE_OPTION_FLAG

FROM

    vhet.dh_subdeal_product_structure t

WHERE

    t.TYPE in ('Pricing Component' 'Value Component') and

    t.PRODUCT_ATTRIBUTE in ('ANNUAL_USAGE' 'TERM_LOAD' 'MARGIN' 'TOTAL_COGS' 'BROKER_FEE' 'CONTRACT_PRICE'

            'ANNUAL_PEAK' 'REVENUE' 'PORTAL_FEE') and

    t.DEAL_OPTION_ID in (&deal_option.) and

    t.VERSION_CURRENT_YN = 'Y'

ORDER BY

    t.DEAL_OPTION_ID;

quit;

New Error:

ERROR: PROC SQL requires any created table to have at least 1 column.

The macro variable &deal_option. doesn't seem to be the problem as that was my first guess. I'm not sure why the columns are now the issue.

Just for kicks, this is the macro variable code:

proc sql;

select vhet_subdeal

into: deal_option separated by " "

from keep_subdeal_id;

quit;

Any ideas guys/gals?

DH

Respected Advisor
Posts: 4,930

Re: Change in SQL from 9.1 to 9.3

Try separating the literals in your IN conditions with commas, including the &deal_option list. - PG

PG
New Contributor
Posts: 4

Re: Change in SQL from 9.1 to 9.3

Tried it to no avail. I replaced the macro variable with just one of the numeric values it stores and the error is the same. If I take out the Where statement, the query runs, but the table is gigantic.

Also, thank you for your time. It is much appreciated.

Super User
Posts: 3,257

Re: Change in SQL from 9.1 to 9.3

I would try puting the WHERE statement back line by line until it fails. You should then be able to isolate where the problem lies. To speed up the testing add the INOBS = option to your PROC SQL statement.

New Contributor
Posts: 4

Re: Change in SQL from 9.1 to 9.3

I appreciate that. The problem doesn't look to be the code. I already attempted the inobs and it's getting hung on the table. I can't read from the table in 9.3.. Sounds like it could be a settings issue between my versions.

PROC Star
Posts: 7,487

Re: Change in SQL from 9.1 to 9.3

As long as deal_option_id is numeric, your code appears to work on 9.3.

New Contributor
Posts: 4

Re: Change in SQL from 9.1 to 9.3

Thanks Arthur. I think it must be an internal systems issue. No idea what it would be, but it has to be something.

Thanks to everyone for there time.

Respected Advisor
Posts: 4,930

Re: Change in SQL from 9.1 to 9.3

I'm baffled. Try removing the WHERE conditions one by one... - PG

PG
Contributor
Posts: 42

Re: Change in SQL from 9.1 to 9.3

Hi,

I think that if the orginal table vhet.dh_subdeal_product_structure was created by SAS 9.1there could be some problems in reading it by SAS 9.3. If it is possible try creating a copy for example in work library by datastep

data dh_subdeal_product_structure_copy;

set vhet.dh_subdeal_product_structureů

run;

and try to run the query reading from this copy of your original dataset.

Jakub

Ask a Question
Discussion stats
  • 8 replies
  • 810 views
  • 8 likes
  • 5 in conversation