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;
What does your output look like? And how does it differ from the desired output?
@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
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.
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 ?
What part of your code/log did not work as desired ?
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.
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.
You answered only to
- describe what the code should do
You have not supplied enough information to help you.
See my previous post.
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);
Thanks for the replay,
Yes has the right to delete and it successfully deletes.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.