BookmarkSubscribeRSS Feed
DHeady
Calcite | Level 5

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

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
DHeady
Calcite | Level 5

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.

SASKiwi
PROC Star

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.

DHeady
Calcite | Level 5

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.

art297
Opal | Level 21

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

DHeady
Calcite | Level 5

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.

PGStats
Opal | Level 21

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

PG
chrej5am
Quartz | Level 8

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

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
  • 8 replies
  • 1668 views
  • 8 likes
  • 5 in conversation