topic Re: Change in SQL from 9.1 to 9.3 in SAS Procedures

Change in SQL from 9.1 to 9.3

DHeady — Wed, 03 Apr 2013 15:21:33 GMT

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?

Re: Change in SQL from 9.1 to 9.3

PGStats — Wed, 03 Apr 2013 15:34:14 GMT

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

Re: Change in SQL from 9.1 to 9.3

DHeady — Wed, 03 Apr 2013 18:47:02 GMT

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.

Re: Change in SQL from 9.1 to 9.3

SASKiwi — Wed, 03 Apr 2013 19:03:39 GMT

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.

Re: Change in SQL from 9.1 to 9.3

DHeady — Wed, 03 Apr 2013 19:07:16 GMT

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.

Re: Change in SQL from 9.1 to 9.3

PGStats — Wed, 03 Apr 2013 19:10:47 GMT

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

Re: Change in SQL from 9.1 to 9.3

art297 — Wed, 03 Apr 2013 20:41:21 GMT

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

Re: Change in SQL from 9.1 to 9.3

DHeady — Wed, 03 Apr 2013 20:46:22 GMT

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.

Re: Change in SQL from 9.1 to 9.3

chrej5am — Thu, 04 Apr 2013 10:46:38 GMT

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