introducing returnval from script 1 as select criterion for script 2

[karlomutschler]

hi and good day,

need to develop an updateroutine for our report data.

Script 1 gathers the data from a definite starting point eg. start of financial year to date.
After successful completion ORA-table MaxDate is created.

Script 2 is the update part for Script 1.
According to the value stored in the MaxDate table it will gather the data from the StartDate to CurrDate.

With a proc sql statement we read the data from the MaxDate table and create a SAS-Table MaxDate.

rsubmit;
proc sql;
connect to oracle(user="&user" password="&pwd" path="db" buffsize=200);
create table MaxDate as
select * from connection to oracle
(
SELECT MaxDate
FROM MaxDate
);
quit;
endrsubmit;

This value now has to to be uses to determine &StartDate for Script 2.

rsubmit;
proc sql;
connect to oracle(user="&user" password="&pwd" path="db" buffsize=200);
create table CurrPeriod as
select * from connection to oracle
(
SELECT PRODUCT_SUBSCRIPTION_ID,
SERVICE_CODE,
SALES_ORG_CHANNEL_RD,
SALES_ORG_REGION_RD,
trunc(EFFECTIVE_REPORT_DATE),
NUMBER_OF_ITEMS
FROM ReportData_FACT
WHERE TRANSACTION_TYPE = 'E'
AND EFFECTIVE_REPORT_DATE >= to_date(&StartDate, 'yyyymmdd')
);
quit;
endrsubmit;

How would I accomplish this task

Tips and suggestions would be highly appreciated.

Thanks in advance.
Kind regards
Karlo

 

[ChrisW75]

One nice easy way, assuming there's only 1 value being brought back from that table is this:-

rsubmit;
proc sql;
   connect to oracle(user="&user" password="&pwd" path="db" buffsize=200);
      select * 
      into :STARTDATE
      from connection to oracle
    (
    SELECT     MaxDate
    FROM    MaxDate
    );
    quit;

proc sql;
   connect to oracle(user="&user" password="&pwd" path="db" buffsize=200);
      create table CurrPeriod as 
      select * from connection to oracle
    (
    SELECT     PRODUCT_SUBSCRIPTION_ID,
        SERVICE_CODE,
        SALES_ORG_CHANNEL_RD,
        SALES_ORG_REGION_RD,
        trunc(EFFECTIVE_REPORT_DATE),
        NUMBER_OF_ITEMS
    FROM    ReportData_FACT
    WHERE    TRANSACTION_TYPE = 'E'
    AND    EFFECTIVE_REPORT_DATE >= to_date(&STARTDATE, 'yyyymmdd')
    );
    quit;

endrsubmit;

The "into" statement loads the result into a macro variable directly. The macro variable name needs to be prefixed with a : here.
That should do the trick.

When using this for other purposes, you may need to know the following.
1 - If you have multiple variables/columns being brought back, youlist multiple macro variable names (each prefixed with in order to load each one.
2 - If you have multiple records coming back, use "separated by" after the macro variable name to separate the values with a delimiter.
ie

  into :VARS separated by ","

will bring back a list of the values separated by commas.

I hope this helps.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.