BookmarkSubscribeRSS Feed
ebad_shakeeb
Fluorite | Level 6

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;

14 REPLIES 14
PeterClemmensen
Tourmaline | Level 20

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

Shmuel
Garnet | Level 18

@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

 

ebad_shakeeb
Fluorite | Level 6

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.

 

 

Shmuel
Garnet | Level 18

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 ?

Shmuel
Garnet | Level 18

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

ebad_shakeeb
Fluorite | Level 6

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

Shmuel
Garnet | Level 18
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.
ebad_shakeeb
Fluorite | Level 6

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.

 

 

 

Shmuel
Garnet | Level 18

You answered only to 

- describe what the code should do

You have not supplied enough information to help you.

See my previous post.

kiranv_
Rhodochrosite | Level 12

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);
ebad_shakeeb
Fluorite | Level 6

Thanks for the replay,

 

Yes has the right to delete and it successfully deletes.

error_prone
Barite | Level 11
Can't see any step outputting &OUTTABLE. Have you tested the code in base or EG before you created the stored process?
ebad_shakeeb
Fluorite | Level 6
it works fine but did not parse the information in the SAS CI
ebad_shakeeb
Fluorite | Level 6
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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 14 replies
  • 1717 views
  • 1 like
  • 5 in conversation