DATA Step, Macro, Functions and more

Output store procedure

Reply
Occasional Contributor
Posts: 12

Output store procedure

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;

PROC Star
Posts: 1,190

Re: Output store procedure

Posted in reply to ebad_shakeeb

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

Trusted Advisor
Posts: 1,822

Re: Output store procedure

@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, 
     using
options mprint mlogic

 

Occasional Contributor
Posts: 12

Re: Output store procedure

[ Edited ]

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.

 

 

Trusted Advisor
Posts: 1,822

Re: Output store procedure

Posted in reply to ebad_shakeeb

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 ?

Trusted Advisor
Posts: 1,822

Re: Output store procedure

[ Edited ]

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

Occasional Contributor
Posts: 12

Re: Output store procedure

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.

 

Parse the data.png

Trusted Advisor
Posts: 1,822

Re: Output store procedure

Posted in reply to ebad_shakeeb
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.
Occasional Contributor
Posts: 12

Re: Output store procedure

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.

 

 

 

Trusted Advisor
Posts: 1,822

Re: Output store procedure

Posted in reply to ebad_shakeeb

You answered only to 

- describe what the code should do

You have not supplied enough information to help you.

See my previous post.

PROC Star
Posts: 499

Re: Output store procedure

Posted in reply to ebad_shakeeb

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);
Occasional Contributor
Posts: 12

Re: Output store procedure

Thanks for the replay,

 

Yes has the right to delete and it successfully deletes.

Regular Contributor
Posts: 195

Re: Output store procedure

Posted in reply to ebad_shakeeb
Can't see any step outputting &OUTTABLE. Have you tested the code in base or EG before you created the stored process?
Occasional Contributor
Posts: 12

Re: Output store procedure

Posted in reply to error_prone
it works fine but did not parse the information in the SAS CI
Occasional Contributor
Posts: 12

Re: Output store procedure

Posted in reply to error_prone
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;
Ask a Question
Discussion stats
  • 14 replies
  • 308 views
  • 1 like
  • 5 in conversation