topic Re: Output store procedure in SAS Programming

Output store procedure

ebad_shakeeb — Sun, 28 Jan 2018 11:19:26 GMT

I have created the store procedure, will calling it doesn't gives me the desire output, which is the deletion records from INTABLE.

As Tested only the porblem is with &OUTABLE at last...

%global stdcode;
%STPBEGIN;
%MASPINIT;
%Macro InitialNode_Setup;

%if &INTABLE EQ %then
%MA_Set_Output_Subject_ID(&Subject);
%mend InitialNode_Setup;

%MACRO S_MERGE;
LIBNAME SAS_BASE base "C:\CI\SAS_BASE_LIB";
libname mkt oracle path=mkt SCHEMA=CCMKT USER=CCMKT PASSWORD="*************" dbsliceparm=all;
LIBNAME BICRMMA TERADATA SERVER=BITERADATA SCHEMA=DP_BVEW USER=ETICRM PASSWORD="{SAS002}5FF62C162FA4F37B53CC523625AB8AB1" dbsliceparm=all;

%LET std_code=&stdcode;

proc sql;
create table &OUTTABLE.Tn_1 as
select a.subs_id as subs_id from mkt.afa_subs_campaign a, &intable b where a.std_code="&std_code" and a.subs_id=b.subs_id;
run;

%put SQLEXITCODE2=&SQLEXITCODE;
%put SQLOBS2=&SQLOBS;

proc sql;
CREATE TABLE &OUTTABLE.Tn_2 AS
select b.subs_id from bicrmma.sms_opt_out a, bicrmma.cc_subs_basic b, &intable c where a.msisdn=b.acc_nbr and b.subs_id=c.subs_id;
run;

%put SQLEXITCODE1=&SQLEXITCODE;
%put SQLOBS1=&SQLOBS;

proc sql;
create table &OUTTABLE.Tn_3 as
select subs_id as subs_id from &INTABLE ;
run;

%put SQLEXITCODE1=&SQLEXITCODE;
%put SQLOBS1=&SQLOBS;

proc sql;
delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_1);
delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_2);
run;

proc sql noprint;
drop table &outtable;
create table &outtable as
select a.subs_id as subs_id from &INTABLE b, &OUTTABLE.Tn_3 a where a.subs_id=b.subs_id;
quit;

%put SQLEXITCODE3=&SQLEXITCODE;
%put SQLOBS3=&SQLOBS;

%MEND S_MERGE;
%MACOUNT(&OUTTABLE);
%MASTATUS(&_STPWORK.STATUS.TXT);
%STPEND;
%S_MERGE;
%InitialNode_Setup;

Re: Output store procedure

PeterClemmensen — Sun, 28 Jan 2018 12:54:55 GMT

What does your output look like? And how does it differ from the desired output?

Re: Output store procedure

Shmuel — Sun, 28 Jan 2018 13:58:25 GMT

@ebad_shakeeb - few remarks to your post:

1) It is difficult to follow your code. I have copied it here in the {man} icon window. Watch the difference:

%global stdcode;
%STPBEGIN;
%MASPINIT;

%Macro InitialNode_Setup;
    %if &INTABLE EQ %then 
    %MA_Set_Output_Subject_ID(&Subject);
%mend InitialNode_Setup;


%MACRO S_MERGE;
    LIBNAME SAS_BASE base "C:\CI\SAS_BASE_LIB";
    libname mkt oracle path=mkt SCHEMA=CCMKT USER=CCMKT PASSWORD="*************" dbsliceparm=all;
    LIBNAME BICRMMA TERADATA SERVER=BITERADATA SCHEMA=DP_BVEW USER=ETICRM PASSWORD="{SAS002}5FF62C162FA4F37B53CC523625AB8AB1" dbsliceparm=all;

	%LET std_code=&stdcode;

    proc sql;
      create table &OUTTABLE.Tn_1 as
      select a.subs_id as subs_id from mkt.afa_subs_campaign a, &intable b where a.std_code="&std_code" and a.subs_id=b.subs_id;
    run;

    %put SQLEXITCODE2=&SQLEXITCODE;
    %put SQLOBS2=&SQLOBS;

    proc sql;
       CREATE TABLE &OUTTABLE.Tn_2 AS 
       select b.subs_id 
       from bicrmma.sms_opt_out a, 
            bicrmma.cc_subs_basic b, 
	        &intable c    where a.msisdn=b.acc_nbr and b.subs_id=c.subs_id;
    run;

   %put SQLEXITCODE1=&SQLEXITCODE;
   %put SQLOBS1=&SQLOBS;

   proc sql;
      create table &OUTTABLE.Tn_3 as
      select subs_id as subs_id from &INTABLE ;
   run;

   %put SQLEXITCODE1=&SQLEXITCODE;
   %put SQLOBS1=&SQLOBS;

   proc sql;
     delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_1);
     delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_2);
   run;

   proc sql noprint;
     drop table &outtable;
     create table &outtable as
     select a.subs_id as subs_id from &INTABLE b, &OUTTABLE.Tn_3 a where a.subs_id=b.subs_id;
   quit;

   %put SQLEXITCODE3=&SQLEXITCODE;
   %put SQLOBS3=&SQLOBS;
%MEND S_MERGE;

%MACOUNT(&OUTTABLE);
%MASTATUS(&_STPWORK.STATUS.TXT);
%STPEND;
%S_MERGE;
%InitialNode_Setup;

2) Using PROC SQL - end it with QUIT; instead of RUN; - Check your log and see appropriate note.

3) You displayed only two macro codes: InitialNode_Setup and S_merge but you use other macros too, unknown to the forum.

4) You wrote "...calling it doesn't give me the desired output...".

What is your input ? what should the code do? what is the desired output?

Have you checked the log? What warning or error messages are there?

In order that the forum can help you please post:

- sample of your data

- describe what the code should do

- expected output relating to the sample

- the log in case of warnings and errors, usingoptions mprint mlogic

Re: Output store procedure

kiranv_ — Sun, 28 Jan 2018 14:44:14 GMT

just wondering to know, whether you or your group has right to delete records

delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_1);

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 06:08:19 GMT

Thanks for replay...

I am getting the input from upper node (Price Plan) and after that need to exclude those subscriber who doesn't want the campaign and SMS and as well as exclude those subs who already have one marketing benefit offer.

Re: Output store procedure

Shmuel — Tue, 30 Jan 2018 06:41:26 GMT

You answered only to

- describe what the code should do

You have not supplied enough information to help you.

See my previous post.

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 07:02:53 GMT

1: Table mkt.afa_subs_campaign: has the records of all subs who is eligible for marketing campaign (Low Call Rate / Bonus On Recharge), before the eligible subs opt-in for the offer the UIP server check this table either is eligible of not !

2: Table BICRMMA.SMS_OPT_OUT: has the records of subs they requested to not send us SMS or Offer notification.

3: My input (&INTABLE): is subs that activated their MSISDN since last year and since last 3 weeks they did not recharged or top-up and their state is one-way-block (my input from upper node in SAS Customer Intelligence Studio)

4: My Desire out is (&OUTTABLE): the remaining records (&INTABLE) after deletion of records of mkt.afa_subs_campaign & bicrmma.sms_opt_out from &INTABLE;

The issue is that every steps in the macro works fine and no error and warning indication in log file but did not pars the information from process node to temporary cell node or did not show the count on the process node.

I have attached log file as well.

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 06:59:24 GMT

Thanks for the replay,

Yes has the right to delete and it successfully deletes.

Re: Output store procedure

Shmuel — Tue, 30 Jan 2018 07:28:35 GMT

I'm looking at the log (searching fr 'tn_') and found next notes:

NOTE: Table MATABLES.TPI2MPVEQ2KUEMAAPSASDEMOTN_1 created, with 1 rows and 1 columns.
.......
NOTE: Table MATABLES.TPI2MPVEQ2KUEMAAPSASDEMOTN_2 created, with 1 rows and 1 columns.
.......
NOTE: Table MATABLES.TPI2MPVEQ2KUEMAAPSASDEMOTN_3 created, with 4 rows and 1 columns.
.......
MPRINT(S_MERGE):   delete from MATables.TPI2MPVEQ2KUEMAAPsasdemoTn_3 where subs_id in (select subs_id from MATables.TPI2MPVEQ2KUEMAAPsasdemoTn_1);
NOTE: 1 row was deleted from MATABLES.TPI2MPVEQ2KUEMAAPSASDEMOTN_3.
.......
MPRINT(S_MERGE):   delete from MATables.TPI2MPVEQ2KUEMAAPsasdemoTn_3 where subs_id in (select subs_id from MATables.TPI2MPVEQ2KUEMAAPsasdemoTn_2);
NOTE: 1 row was deleted from MATABLES.TPI2MPVEQ2KUEMAAPSASDEMOTN_3.
........
MPRINT(S_MERGE):   create table MATables.TPI2MPVEQ2KUEMAAPsasdemo as select a.subs_id as subs_id from MATables.TPI2MPVEQ2KUE0AAQsasdemo b, MATables.TPI2MPVEQ2KUEMAAPsasdemoTn_3 a where a.subs_id=b.subs_id;
NOTE: Table MATABLES.TPI2MPVEQ2KUEMAAPSASDEMO created, with 2 rows and 1 columns.
........
SQLOBS3=2

Am I right - your final output has 2 observations.

Where is the problem ?

What is your desired output ?

Re: Output store procedure

Shmuel — Tue, 30 Jan 2018 07:30:40 GMT

What part of your code/log did not work as desired ?

Re: Output store procedure

error_prone — Tue, 30 Jan 2018 07:39:23 GMT

Can't see any step outputting &OUTTABLE. Have you tested the code in base or EG before you created the stored process?

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 08:13:01 GMT

Thanks for your replay Mr. Shmuel,

Yes i expect the same to have 2 records and the last table created with two, but in the SAS Customer Intelligence Studio interface the node after process and process node both are zero _null_

proc sql noprint;
drop table &outtable;
create table &outtable as
select a.subs_id as subs_id from &INTABLE b, &OUTTABLE.Tn_3 a where a.subs_id=b.subs_id;
quit;

it create the table but did parse the subs_id to lower node in interface.

Re: Output store procedure

Shmuel — Tue, 30 Jan 2018 08:30:14 GMT

I'm not familiar with SAS Customer Intelligence Studio and have no knowledge of its installation connections.

I hope someone else will help you with next step.

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 09:43:36 GMT

it works fine but did not parse the information in the SAS CI

Re: Output store procedure

ebad_shakeeb — Tue, 30 Jan 2018 12:53:03 GMT

proc sql noprint;
drop table &outtable;
create table &outtable as
select a.subs_id as subs_id from &INTABLE b, &OUTTABLE.Tn_3 a where a.subs_id=b.subs_id;
quit;