Re: SAS macro

sayanapex06 · Posted 03-17-2017 09:51 AM

DATA TDTL;
SET TDTL;
**RENAMING THE TOTAL COST BY SPECIFIC TABLE NAME**;
QDW_USD_AMT_TDTL = QDW_USD_AMT;
;

PROC MEANS NOPRINT SUM DATA=TDTL;
   BY DATA_FILE;
   VAR QDW_USD_AMT_TDTL;
OUTPUT OUT= TDTL_SUM SUM= QDW_USD_AMT_TDTL ;

DATA _NULL_;
SET TDTL_SUM;
CALL SYMPUT('CHECK', QDW_USD_AMT_TDTL);
PUT &CHECK;
;

I have the above code, where I want to put the value of QDW_USD_AMT_TDTL into a macro variable 'CHECK'

but I am getting the following error :

349        SET TDTL_SUM;
350        CALL SYMPUT('CHECK', QDW_USD_AMT_TDTL);
351        PUT &CHECK;
               _
               _
               _
               22
               22
               22
WARNING: Apparent symbolic reference CHECK not resolved.
ERROR 22-322: Expecting a name.
ERROR 22-322: Expecting a name.
ERROR 22-322: Expecting a name.

Please help me with it ,

Thanks.

gamotte · Posted 03-17-2017 09:58 AM

Hello,

the macrovariable created by a call symput only exists after the datastep execution. Add a "run"; to terminate the datastep and use %put to display the macrovariable value.

sayanapex06 · Posted 03-17-2017 10:51 AM

Thanks,

So you mean to say... If I add a run.. Then I can call the macro variable
wherever I want in the code below... I want to use this ahead also... For
some verification

##- Please type your reply above this line. Simple formatting, no
attachments. -##

gamotte · Posted 03-17-2017 11:18 AM

Sorry, i don't understand what you mean by "use this ahead also". You mean use the macrovariable in the datastep ? You can use the variable QDW_USD_AMT_TDTL itself. What is it exactly that you want to do ?

sayanapex06 · Posted 03-17-2017 11:30 AM

See, this cost is coming from a table... I have 8 more tables... And
when i use the data step variables.. To check some conditions.. Its not
fulfilling... As due to all these costs(observations) are coming from
many data step's various variables.. So I want to compute the sum for
each of the table and put each of those data step variables into a macro
variable for checking the condition.. So all I ask.. Is once I declare it
as a macro variable... By putting the run statement.. Can i use it
anywhere in the code.. Whenever I want...?
##- Please type your reply above this line. Simple formatting, no
attachments. -##

gamotte · Posted 03-17-2017 12:11 PM

A macrovariable created inside a macro is local to that macro unless you

declare it as global in some way. On the other hand, macrovariables declared

outside macros are global and can be used anywhere once they have been declared.

%let b=2;

%macro test;

%let a=1; /* inside macro => local */

%if &a. %then %do;

%put Hello !; /*OK*/

%end;

%mend test;

%test;

%put &a.; /* won't work because a is locat to %test */

%put &b.; /* OK */

That said, there is probably no need to use macrovariables here.

Can't you concatenate your datasets and use a by statement to check

your condition on each dataset ?

sayanapex06 · Posted 03-17-2017 12:17 PM

Thanks a lot... !
So in my code.. The call symput is assigning a the value of
qdw_usd_amt_tdtl to check right...

So I can use this &check anywhere in my program right? And it will contain
the value of the qdw_usd_amt_tdtl right? And one mote thing... I also
tried converting a data step variable by %let.. It wont happen.. Can you
answer each of my questions.. Please

##- Please type your reply above this line. Simple formatting, no
attachments. -##

gamotte · Posted 03-17-2017 12:49 PM

Yes, you can use &check anywhere after it has been defined by the datastep.

What do you mean by converting a data step variable by %let ? Can you show an example ?

Macro instructions only perform text substitution, like the C preprocessor. They are used for code generation,

symplifying repetitive tasks. They have no knowledge of what the datasets contain.

The probleme you describe does not seem to need the use of macros or macrovariables.

can you give an example (salmple datasets and the final result you wish to obtain) ?

Here is an example of concatenating two datasets and checking a condition on the sum of variable x

for each dataset :

data a;
input x;
cards;
1
2
3
;
run;

data b;
input x;
cards;
4
5
6
;
run;

data all;
	set a (in=in_a) 
	    b (in=in_b);
	if in_a then dataset="a";
	else dataset="b";
run;

data want;
    set all;
    by dataset;
    retain sumX;

	drop X dataset;

    if first.dataset then sumX=0;

    sumX+x;

    if last.dataset then do;
        if sumX>8 then status="OK";
        else status="KO";
		output;
    end;
run;

sayanapex06 · Posted 03-17-2017 01:08 PM

Actually I am in need of the macro variable as, due to too many data
steps.. The final proc print report is having dots along with the actual
sum... So i need to assign all the sums to different macro variables.. To
be referred later on in the program...

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Reeza · Posted 03-17-2017 02:07 PM

@sayanapex06 wrote:
Actually I am in need of the macro variable as, due to too many data
steps.. The final proc print report is having dots along with the actual
sum... So i need to assign all the sums to different macro variables.. To
be referred later on in the program...

##- Please type your reply above this line. Simple formatting, no
attachments. -##

It's likely then you're doing something incorrectly or inefficiently.

Reeza · Posted 03-17-2017 02:06 PM

@sayanapex06 wrote:
Thanks a lot... !
So in my code.. The call symput is assigning a the value of
qdw_usd_amt_tdtl to check right...

Yes this is correct.

So I can use this &check anywhere in my program right? And it will contain
the value of the qdw_usd_amt_tdtl right?

You can use it AFTER the creation step runs, not before, but yes, you can use it anywhere in the program as long as it results in SAS code.

And one mote thing... I also
tried converting a data step variable by %let.. It wont happen

Correct, %LET won't work in a data step, which is why you use CALL SYMPUTX()

Can you
answer each of my questions.. Please

Ask your questions in a clear and concise manner and you'll get answers faster. As is, I've taken more time to format my answer and provide a detailed answer than you appear to have taken in asking your question. Complete sentences are nice as well. Ask questions one a time, show what you have and what you need as simply as possible. Trying to be vague doesn't help. Since you're relatively new you get a pass, but this suggestion doesn't apply to just this forum.

sayanapex06 · Posted 03-17-2017 02:10 PM

Thanks again!
I am sorry for the inconvenience caused.
Currently I am from my phone so the questions are in a haphazard way. Once
I will be back to work, I will be asking in a systematic way

##- Please type your reply above this line. Simple formatting, no
attachments. -##

sayanapex06 · Posted 03-17-2017 11:12 PM

@gamotte @Reeza @gamotte @Tom

Hi All,

Thanks for your useful information. I am getting what I am supposed to get now, but I am facing an issue later on in the program,

Can you help me with this :

The code is :

%PUT &TDETAIL;
%PUT &TODETAIL;
%PUT &TOBILLER;
%PUT &TOPRC01;
%PUT &TOPRC02;
%PUT &TOPRC03;
%PUT &TOPRC04;
%PUT &TOPRC05;
%PUT &TOPRC06;
%PUT &TOPRC07;

%IF &TOPRC01=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 010;
%RETURN;
;

Error is :

2797        %IF &TOPRC01=&TDETAIL=&TODETAIL=&TOBILLER %THEN
ERROR: The %IF statement is not valid in open code.
ERROR: The %IF statement is not valid in open code.
ERROR: The %IF statement is not valid in open code.
2798        %ABORT 010;
2799        %RETURN;
ERROR: The %RETURN statement is not valid in open code.
ERROR: The %RETURN statement is not valid in open code.
ERROR: The %RETURN statement is not valid in open code.
2800         ;

I am getting all the values in those macro variables TDETAIL,TODETAIL,TOBILLER,TOPRC01, etc etc

Now I want my program to abend based on some conditions,

And I am getting this error.

Reeza · Posted 03-17-2017 11:38 PM

Error is clear. You can't use %IF except in a macro and your code shows no macro.

Your %IF condition won't evaluate as you expect it to either.

sayanapex06 · Posted 03-18-2017 12:59 AM

So can You Please tell me how to feed my If condition to the global macro variables ?

I dont want to change the code.

Here lies the code :

//CHSSCKTO JOB EI4605,                                                  JOB19334
//        'DIONNE',
//         CLASS=S,
//         MSGCLASS=X,
//         MSGLEVEL=(1,1),
//         COND=(0,NE),
//         NOTIFY=&SYSUID,
//         RESTART=*
/*JOBPARM K=0,LINES=80,COPIES=1,LINECT=0
//* $ACFJ219 ACF2 ACTIVE CHRYHILL                                           ACF2
//*********************************************************************
//**deleting and allocating some non-usable files for verification
//**incase the job fails and we have to check
//*********************************************************************
//D02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TDETAIL.AGGR.COC,
//            DISP=(OLD,DELETE,DELETE)
//A02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TDETAIL.AGGR.COC,
//             DISP=(,CATLG,CATLG),
//             UNIT=SYSDA,
//             SPACE=(TRK,(5,5),RLSE),
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=6400)
//*********************************************************************
//*********************************************************************
//D02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TOBILLER.ALL.COC,
//            DISP=(OLD,DELETE,DELETE)
//A02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TOBILLER.ALL.COC,
//             DISP=(,CATLG,CATLG),
//             UNIT=SYSDA,
//             SPACE=(TRK,(5,5),RLSE),
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=6400)
//*********************************************************************
//*********************************************************************
//D02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TODETAIL.AGGR.COC,
//            DISP=(OLD,DELETE,DELETE)
//A02      EXEC PGM=IEFBR14
//DD1      DD DSN=CHMICST.TODETAIL.AGGR.COC,
//             DISP=(,CATLG,CATLG),
//             UNIT=SYSDA,
//             SPACE=(TRK,(5,5),RLSE),
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=6400)
//*********************************************************************
//********************************************************************
//* VERIFY THE QDW TOTALS
//********************************************************************
//VERIFY   EXEC SAS,WORK='65535,65535',SORT=2400
//*        LSYSOUT='*',RSYSOUT='*',RSIZE=8M,SORT=999,
//*        WORK='50000,50000'
//*
//A2ZSRC     DD DISP=SHR,
//           DSN=CHMICST.MICS.SRCLIB.A2Z.PORTAL
//A2ZSRC1    DD DISP=SHR,
//           DSN=CHMICST.NG99RLT.NEWDEV.CODES1
//*
//* INPUT FILES
//*
//TODTL    DD DISP=SHR,DSN=CHMICST.SSO.PORTAL.QDW.TOTHRDTL
//TDTL     DD DISP=SHR,DSN=CHMICST.SSO.PORTAL.QDW.TDETAIL
//TOTHBLR DD DISP=SHR,DSN=CHMICST.SSO.PORTAL.QDW.TOTHBLR
//A01DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC01
//A02DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC02
//A03DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC03
//A04DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC04
//A05DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC05
//A06DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC06
//A07DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC07
//A08DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC08
//A09DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC09
//A10DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC10
//A11DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC11
//A12DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC12
//A13DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC13
//A14DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC14
//A15DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC15
//A16DAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC16
//RPTDAT   DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRCRPT
//A2Z      DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC01
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC02
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC03
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC04
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC05
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC06
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC07
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC08
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC09
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC10
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC11
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC12
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC13
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC14
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC15
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC16
//         DD DISP=SHR,DSN=CHMICST.A2Z.PORTAL.SSOBLNG.TOPRCRPT
//*
//* OUTPUT FILES
//*
//WTODTL   DD DISP=SHR,DSN=CHMICST.TODETAIL.AGGR.COC *non-usable*
//WTDTL    DD DISP=SHR,DSN=CHMICST.TDETAIL.AGGR.COC *non-usable*
//WTOTHBLR DD DISP=SHR,DSN=CHMICST.TOBILLER.ALL.COC *non-usable*
//SASLIST DD SYSOUT=*
//TOTDATA DD DUMMY
//*
//*
//*
//SYSIN    DD *
OPTIONS SOURCE SOURCE2 NOCENTER;
     %INCLUDE 'CHMICST.SAS.CODE(NUMDCML)';
     %INCLUDE 'CHMICST.SAS.CODE(FSFBUC)';
*-------------------------------------------- QDW DETAIL AND SUMMARY ;

****************************T_DETAIL*********************************;

DATA TDTL; * SUMMARIZED QDW DATA;

LENGTH DATA_FILE $30.;
KEEP DATA_FILE QDW_USD_AMT QDW_CUR_AMT;
FORMAT QDW_USD_AMT QDW_CUR_AMT 16.2;

INFILE TDTL DELIMITER='~';

INPUT @1 COMPCD                : $100.
           LOCATION              : $100.
           BUC                   : $6.
           ADN                   : $100.
           DEPT                  : $100.
           COMP                  : $100.
           OPT_ACCT              : $100.
           MINOR_ACCT            : $100.
           BYRMO                 : $100.
           ITEM                  : $100.
           INVOICE_NUM           : $100.
           FYRWK                 : $100.
           XQDW_USD_AMT          : $100.
           UNITS                 : $100.
           RATE                  : $100.
           UPLIFT                : $100.
           VAT                   : $100.
           VAT_LOC_CURR          : $100.
           XQDW_CUR_AMT          : $100.
           CURRENCY              : $100.
           MOR                   : $100.
           REFUND_MO             : $100.
           DIRECT_AMT            : $100.
           INDIRECT_AMT          : $100.
           COST_CENTER           : $100.
           OTHER_INFO            : $100.
           PROD_DETAIL           : $100.
           ENVIRONMENT           : $100.
           SVR_FUNC              : $100.
           ORDER_NUM             : $100.
           SPCL_BILLNG_CD        : $100.
           APP_NAME              : $100.
           MF_SUB_BUSINESS       : $100.
           RES_UNIT_NAME         : $100.
           REGION_IND            : $100.
           NORM_CPU_HRS          : $100.
           CONNECT_HRS           : $100.
           BEHALF_OF             : $100.
           SYSTEM                : $100.
           GOLDID                : $100.
           P_NP                  : $100.
           RES_UNIT_UNITS        : $100.
           TAPE_MOUNTS           : $100.
           TAPE_MNTS_CART        : $100.
           TAPE_MNTS_ROUND       : $100.
           TSO_LOGON_ID          : $100.
           JESJOBNO              : $100.
           START_CREATE_DATE     : $100.
           STOP_LAST_USED_DATE   : $100.
           JOB_CLASS             : $100.
           JOB_SYS_ABND_IND      : $100.
           JOB_TERM_CD           : $100.
           MGMNT_CLASS           : $100.
           TAPE_JOB_NAME         : $100.
           RELEASE_DATE          : $100.
           TAPE_RET_DAYS         : $100.
           CHGNUM                : $100.
           ADDTL_CATGRY          : $100.
           JOB_USR_ABND_IND      : $100.
           GST                   : $100.
           GST_LOC_CURR          : $100.
           LIQ_VIA_STD_BILLING   : $100.
           BILLINFO_LINK         : $100.
           BILLING_LOCATION      : $100.
           BILLING_QUARTER       : $100.
           REVERSAL              : $100.
           COUNTRY_REGION_CODE   : $100.
           TYPE_OF_BATCH         : $100.
           IBS_INVOICE_DATE      : $100.
           IBS_LINE_NUMBER       : $100.
           SERVICE_TYPE          : $100.
           ROOT_CAUSE_CODE       : $100.
           VENDOR_CODE           : $100.
           VAT_DESCRIPTION_CODE : $100.
           TAX_ADN               : $100.
           OFFSET_BUC            : $100.
           ;

* IF THERE IS NO DECIMAL PLACE ON THE AMOUNT, ADD ONE;
POS = INDEXC(XQDW_USD_AMT,'.');
IF POS = 0 THEN XQDW_USD_AMT = TRIM(XQDW_USD_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
QDW_USD_AMT = XQDW_USD_AMT;

POS = INDEXC(XQDW_CUR_AMT,'.');
IF POS = 0 THEN XQDW_CUR_AMT = TRIM(XQDW_CUR_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
QDW_CUR_AMT = XQDW_CUR_AMT;

LENGTH FROM_BUC_FLAG $4.;
OFFSET_BUC = TRIM(LEFT(UPCASE(OFFSET_BUC)));
FROM_BUC_FLAG = PUT(OFFSET_BUC,$FSFBUC.);

IF FROM_BUC_FLAG   = 'BILL' THEN DO;
    DATA_FILE = 'QDW-TDTL (BILL)';
    OUTPUT TDTL ;
    IF INDEX(INVOICE_NUM,'ZZZ-') > 0 THEN DO ;
      IF INDEX(INVOICE_NUM,'-OAR-') > 0 THEN ;
      ELSE DO ;
        DATA_FILE = 'QDW-TDTL (BILL-NON OAR ZZZ)';
        OUTPUT TDTL ;
      END;
    END;
    ELSE DO ;
        DATA_FILE = 'QDW-TDTL (BILL-IBS)';
        OUTPUT TDTL ;
    END;
END;
ELSE IF FROM_BUC_FLAG   = 'AGGR' THEN DO;
    DATA_FILE = 'QDW-TDTL (AGGR)';
    OUTPUT TDTL ;
END;
ELSE DO;
    DATA_FILE = 'QDW-TDTL (QDWR)';
    OUTPUT TDTL ;
END;
RUN;

PROC SORT DATA=TDTL;
BY DATA_FILE;

DATA TDTL;
SET TDTL;
**RENAMING THE TOTAL COST BY SPECIFIC TABLE NAME**;
QDW_USD_AMT_TDTL = QDW_USD_AMT;
;

PROC MEANS NOPRINT SUM DATA=TDTL;
BY DATA_FILE;
VAR QDW_USD_AMT_TDTL QDW_CUR_AMT;
OUTPUT OUT= TDTL_SUM SUM= QDW_USD_AMT_TDTL QDW_CUR_AMT;

PROC PRINT DATA=TDTL_SUM NOOBS;
VAR QDW_USD_AMT_TDTL;

DATA _NULL_;
SET TDTL_SUM;
CALL SYMPUT('TDETAIL', QDW_USD_AMT_TDTL);
RUN;

%PUT &TDETAIL;
;
************************t_other_detail*********************;

DATA TODTL; * DETAIL QDW DATA;

LENGTH DATA_FILE $30.;
KEEP DATA_FILE QDW_USD_AMT QDW_CUR_AMT ODTL_LINECNT;
FORMAT QDW_USD_AMT QDW_CUR_AMT 16.2;

INFILE TODTL DELIMITER='~';

INPUT @1 COMPCD                : $100.
           LOCATION              : $100.
           BUC                   : $100.
           ADN                   : $100.
           DEPT                  : $100.
           COMP                  : $100.
           OPT_ACCT              : $100.
           MINOR_ACCT            : $100.
           BYRMO                 : $100.
           ITEM                  : $100.
           INVOICE_NUM           : $100.
           FYRWK                 : $100.
           XQDW_USD_AMT          : $100.
           UNITS                 : $100.
           RATE                  : $100.
           UPLIFT                : $100.
           VAT                   : $100.
           VAT_LOC_CURR          : $100.
           XQDW_CUR_AMT          : $100.
           CURRENCY              : $100.
           MOR                   : $100.
           REFUND_MO             : $100.
           DIRECT_AMT            : $100.
           INDIRECT_AMT          : $100.
           COST_CENTER           : $100.
           OTHER_INFO            : $100.
           PROD_DETAIL           : $100.
           ENVIRONMENT           : $100.
           SVR_FUNC              : $100.
           ORDER_NUM             : $100.
           SPCL_BILLNG_CD        : $100.
           APP_NAME              : $100.
           MF_SUB_BUSINESS       : $100.
           RES_UNIT_NAME         : $100.
           REGION_IND            : $100.
           NORM_CPU_HRS          : $100.
           CONNECT_HRS           : $100.
           BEHALF_OF             : $100.
           SYSTEM                : $100.
           GOLDID                : $100.
           P_NP                  : $100.
           RES_UNIT_UNITS        : $100.
           TAPE_MOUNTS           : $100.
           TAPE_MNTS_CART        : $100.
           TAPE_MNTS_ROUND       : $100.
           TSO_LOGON_ID          : $100.
           JESJOBNO              : $100.
           START_CREATE_DATE     : $100.
           STOP_LAST_USED_DATE   : $100.
           JOB_CLASS             : $100.
           JOB_SYS_ABND_IND      : $100.
           JOB_TERM_CD           : $100.
           MGMNT_CLASS           : $100.
           TAPE_JOB_NAME         : $100.
           RELEASE_DATE          : $100.
           TAPE_RET_DAYS         : $100.
           CHGNUM                : $100.
           ADDTL_CATGRY          : $100.
           JOB_USR_ABND_IND      : $100.
           GST                   : $100.
           GST_LOC_CURR          : $100.
           LIQ_VIA_STD_BILLING   : $100.
           BILLINFO_LINK         : $100.
           BILLING_LOCATION      : $100.
           BILLING_QUARTER       : $100.
           REVERSAL              : $100.
           COUNTRY_REGION_CODE   : $100.
           TYPE_OF_BATCH         : $100.
           IBS_INVOICE_DATE      : $100.
           IBS_LINE_NUMBER       : $100.
           SERVICE_TYPE          : $100.
           ROOT_CAUSE_CODE       : $100.
           VENDOR_CODE           : $100.
           VAT_DESCRIPTION_CODE : $100.
           TAX_ADN               : $100.
           OFFSET_BUC            : $100.
           ;

* IF THERE IS NO DECIMAL PLACE ON THE AMOUNT, ADD ONE;
POS = INDEXC(XQDW_USD_AMT,'.');
IF POS = 0 THEN XQDW_USD_AMT = TRIM(XQDW_USD_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
QDW_USD_AMT = XQDW_USD_AMT;

POS = INDEXC(XQDW_CUR_AMT,'.');
IF POS = 0 THEN XQDW_CUR_AMT = TRIM(XQDW_CUR_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
QDW_CUR_AMT = XQDW_CUR_AMT;

ODTL_LINECNT = 1;

LENGTH FROM_BUC_FLAG $4.;
OFFSET_BUC = TRIM(LEFT(UPCASE(OFFSET_BUC)));
FROM_BUC_FLAG = PUT(OFFSET_BUC,$FSFBUC.);

IF FROM_BUC_FLAG   = 'BILL' THEN DO;
    DATA_FILE = 'QDW-TODTL (BILL)';
    OUTPUT TODTL;
    IF INDEX(INVOICE_NUM,'ZZZ-') > 0 THEN DO;
      IF INDEX(INVOICE_NUM,'-OAR-') > 0 THEN ;
      ELSE DO ;
        DATA_FILE = 'QDW-TODTL (BILL-NON OAR ZZZ)';
        OUTPUT TODTL;
      END;
    END;
    ELSE DO ;
        DATA_FILE = 'QDW-TODTL (BILL-IBS)';
        OUTPUT TODTL;
    END;
END;
ELSE IF FROM_BUC_FLAG   = 'AGGR' THEN DO;
    DATA_FILE = 'QDW-TODTL (AGGR)';
    OUTPUT TODTL;
END;
ELSE DO;
    DATA_FILE = 'QDW-TODTL (QDWR)';
    OUTPUT TODTL;
END;
RUN;

PROC SORT DATA=TODTL;
BY DATA_FILE;

DATA TODTL;
SET TODTL;
** RENAME THE TOTAL VARIABLE FOR SPECIFIC TABLE**;
QDW_USD_AMT_TODTL = QDW_USD_AMT;
;
PROC MEANS NOPRINT SUM DATA=TODTL;
BY DATA_FILE;
VAR QDW_USD_AMT_TODTL QDW_CUR_AMT ODTL_LINECNT;
OUTPUT OUT= TODTL_SUM SUM= QDW_USD_AMT_TODTL QDW_CUR_AMT
ODTL_LINECNT;

PROC PRINT DATA =TODTL_SUM NOOBS;
VAR QDW_USD_AMT_TODTL;

DATA _NULL_;
SET TODTL_SUM;
CALL SYMPUT('TODETAIL', QDW_USD_AMT_TODTL);
RUN;

%PUT &TODETAIL;
;

***********************t_toher_biller********************;

DATA OTHBLR; * TOTHBLR QDW DATA;

LENGTH DATA_FILE $30.;
KEEP DATA_FILE USD_AMT CUR_AMT CUR_OFF_AMT;
FORMAT USD_AMT CUR_AMT CUR_OFF_AMT 16.2 ;

INFILE TOTHBLR DELIMITER='~';

INPUT @1 ITEMS_KEY                : $100.
           ONEBILLER_INVOICE_NUMBER : $100.
           IBS_CREATION_DATE        : $100.
           BUC                      : $100.
           ADN                      : $100.
           ONEBILLER_UPDATED_BUC    : $100.
           ONEBILLER_UPDATED_ADN    : $100.
           BILLER_STATUS            : $100.
           DROPPED_REASON           : $100.
           CURRENCY_CODE            : $100.
           MOR                      : $100.
           XQDW_USD_AMT             : $100.
           XQDW_CUR_AMT             : $100.
           TAX                      : $100.
           TAX_LOCAL_CURRENCY       : $100.
           TAX_PCT                  : $100.
           UPLIFT_USD               : $100.
           UPLIFT_LOCAL_CURRENCY    : $100.
           UPLIFT_PCT               : $100.
           ;

* IF THERE IS NO DECIMAL PLACE ON THE AMOUNT, ADD ONE;
POS = INDEXC(XQDW_USD_AMT,'.');
IF POS = 0 THEN XQDW_USD_AMT = TRIM(XQDW_USD_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
USD_AMT = XQDW_USD_AMT;

POS = INDEXC(XQDW_CUR_AMT,'.');
IF POS = 0 THEN XQDW_CUR_AMT = TRIM(XQDW_CUR_AMT) || '.';
* CONVERT THE INPUT STRING TO A NUMERIC;
CUR_AMT = XQDW_CUR_AMT;

DATA_FILE = 'ONEBILLER (ALL)';
OUTPUT OTHBLR ;

ONEBILLER_INVOICE_NUMBER =
                  TRIM(LEFT(UPCASE(ONEBILLER_INVOICE_NUMBER)));
IF INDEX(ONEBILLER_INVOICE_NUMBER, 'AGGREGATING') > 0 THEN DO ;
       DATA_FILE = 'ONEBILLER (AGGR)';
       OUTPUT OTHBLR ;
END ;

RUN;

PROC SORT DATA=OTHBLR;
BY DATA_FILE;

DATA OTHBLR;
SET OTHBLR;
** RENAMING THE TOTAL COST VARIABLE ON THE NAME OF THE TABLE**;
QDW_USD_AMT_TOTHBLR= USD_AMT;
;
PROC MEANS NOPRINT SUM DATA=OTHBLR;
BY DATA_FILE;
ID CUR_OFF_AMT;
VAR QDW_USD_AMT_TOTHBLR CUR_AMT ;
OUTPUT OUT= OTHBLR_SUM SUM= QDW_USD_AMT_TOTHBLR CUR_AMT ;

PROC PRINT DATA=OTHBLR_SUM (OBS=1) NOOBS;
VAR QDW_USD_AMT_TOTHBLR;

DATA _NULL_;
SET OTHBLR_SUM;
CALL SYMPUT('TOBILLER', QDW_USD_AMT_TOTHBLR);
RUN;

%PUT &TOBILLER;
;

DATA _NULL_;
SET TDTL_SUM ;
FILE WTDTL NOPRINT NOTITLES MOD;
PUT @1 'QDW_USD_AMT_TDTL';
PUT @1 QDW_USD_AMT_TDTL ;
;
DATA _NULL_;
SET TODTL_SUM ;
FILE WTODTL NOPRINT NOTITLES MOD;
PUT @1 'QDW_USD_AMT_TODTL';
PUT @1 QDW_USD_AMT_TODTL;
;
DATA _NULL_;
SET OTHBLR_SUM;
FILE WTOTHBLR NOPRINT NOTITLES MOD;
PUT @1 'QDW_USD_AMT_TOTHBLR';
PUT @1 QDW_USD_AMT_TOTHBLR;
;

*****************GETTING THE COST FROM THE TOPRC FILES********;

%LET A2ZDAT = 'A2ZDAT17'; * NUMBER OF INPUT/TOTAL FILES ;

* INDIVIDUAL A2Z ;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC01';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC01';                ;
%LET DFILE       = 'A01';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC1;
SET DATA_TOTAL;
SUM1=U_AMOUNT;
PUT @4 SUM1;
;

DATA _NULL_;
SET TOPRC1;
CALL SYMPUT('TOPRC01', SUM1);
RUN;

%PUT &TOPRC01;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC02';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC02';                ;
%LET DFILE       = 'A02';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC2;
SET DATA_TOTAL;
SUM2=U_AMOUNT;
PUT @4 SUM2;
;

DATA _NULL_;
SET TOPRC2;
CALL SYMPUT('TOPRC02', SUM2);
RUN;

%PUT &TOPRC02;

;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC03';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC03';                ;
%LET DFILE       = 'A03';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC3;
SET DATA_TOTAL;
SUM3=U_AMOUNT;
PUT @4 SUM3;
;

DATA _NULL_;
SET TOPRC3;
CALL SYMPUT('TOPRC03', SUM3);
RUN;

%PUT &TOPRC03;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC04';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC04';                ;
%LET DFILE       = 'A04';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC4;
SET DATA_TOTAL;
SUM4=U_AMOUNT;
PUT @4 SUM2;
;

DATA _NULL_;
SET TOPRC4;
CALL SYMPUT('TOPRC04', SUM4);
RUN;

%PUT &TOPRC04;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC05';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC05';                ;
%LET DFILE       = 'A05';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC5;
SET DATA_TOTAL;
SUM5=U_AMOUNT;
PUT @4 SUM5;
;

DATA _NULL_;
SET TOPRC5;
CALL SYMPUT('TOPRC05', SUM5);
RUN;

%PUT &TOPRC05;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC06';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC06';                ;
%LET DFILE       = 'A06';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC6;
SET DATA_TOTAL;
SUM6=U_AMOUNT;
;

DATA _NULL_;
SET TOPRC6;
CALL SYMPUT('TOPRC06', SUM6);
RUN;

%PUT &TOPRC06;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC07';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC07';                ;
%LET DFILE       = 'A07';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC7;
SET DATA_TOTAL;
SUM7=U_AMOUNT;
;

DATA _NULL_;
SET TOPRC7;
CALL SYMPUT('TOPRC07', SUM7);
RUN;

%PUT &TOPRC07;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC08';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC08';                ;
%LET DFILE       = 'A08';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC8;
SET DATA_TOTAL;
SUM8=U_AMOUNT;
;

DATA _NULL_;
SET TOPRC8;
CALL SYMPUT('TOPRC08', SUM8);
RUN;

%PUT &TOPRC08;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC09';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC09';                ;
%LET DFILE       = 'A09';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC9;
SET DATA_TOTAL;
SUM9=U_AMOUNT;
;

DATA _NULL_;
SET TOPRC9;
CALL SYMPUT('TOPRC09', SUM9);
RUN;

%PUT &TOPRC09;
;

*------------------------SOME NOTES---------------------------------;

*---------assigned macro variables for only 9 toprcs,cause thats;
*---------the maximum we get, if we get those many records;

*---------add some macro variables in the format given below;
*---------for all the other toprc** files , just change the numbers;
*---------accordingly;

*****************FORMAT*******************;
*    DATA _NULL_;
*    SET TOPRC9; *(change the numbers of the toprcs for each file);
*    CALL SYMPUT('TOPRC09', SUM9);
*    RUN;
*
*    %PUT &TOPRC09; *(change the numbers of the toprcs for each file);
*     ;
******************************************;

*------------------------END OF NOTES---------------------------------;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC10';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC10';                ;
%LET DFILE       = 'A10';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC10;
SET DATA_TOTAL;
SUM10=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC11';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC11';                ;
%LET DFILE       = 'A11';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC11;
SET DATA_TOTAL;
SUM11=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC12';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC12';                ;
%LET DFILE       = 'A12';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC12;
SET DATA_TOTAL;
SUM12=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC13';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC13';                ;
%LET DFILE       = 'A13';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC13;
SET DATA_TOTAL;
SUM13=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC14';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC14';                ;
%LET DFILE       = 'A14';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC14;
SET DATA_TOTAL;
SUM14=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC15';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC15';                ;
%LET DFILE       = 'A15';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC15;
SET DATA_TOTAL;
SUM15=U_AMOUNT;
;

%LET FILENAME    = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC16';
%LET SPREADSHEET = 'CHMICST.A2Z.PORTAL.SSOBLNG.TOPRC16';                ;
%LET DFILE       = 'A16';
%INCLUDE A2ZSRC1(PROCBAL);
DATA TOPRC16;
SET DATA_TOTAL;
SUM16=U_AMOUNT;
;

**************CHECK TOPRC'S DATA WITH QDW TOTALS DATA****;
**************and set return codes based on them*****;

%PUT &TDETAIL;
%PUT &TODETAIL;
%PUT &TOBILLER;
%PUT &TOPRC01;
%PUT &TOPRC02;
%PUT &TOPRC03;
%PUT &TOPRC04;
%PUT &TOPRC05;
%PUT &TOPRC06;
%PUT &TOPRC07;
%PUT &TOPRC08;

%IF &TOPRC01=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 010;
%RETURN;
;

*********************************check from here**************************************
%IF &TOPRC02=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 020;
%RETURN;
;
%IF &TOPRC03=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 030;
%RETURN;
;
%IF &TOPRC04=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 040;
%RETURN;
;
%IF &TOPRC05=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 050;
%RETURN;
;
%IF &TOPRC06=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 060;
%RETURN;
;
%IF &TOPRC07=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 070;
%RETURN;
;
%IF &TOPRC08=&TDETAIL=&TODETAIL=&TOBILLER %THEN
%ABORT 080;
%RETURN;
;

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away