Here is the code I'm trying to update -
"LIBNAME DTA "/opt/app/sas/WS_files/";
Proc Import Datafile="/opt/app/sas/WS_files/CATID Listing.csv"
OUT=SAF_TABLE DBMS=CSV REPLACE;
Run;
Proc Sort Data=CAT_TABLE;
BY CATID;
Proc Import Datafile="/opt/app/sas/WS_files/FY22 EMPID Samples.csv"
OUT=EMPID_TABLE DBMS=CSV REPLACE;
Run;
Data EMPID_TABLE_ID;
Set EMPID_TABLE;
Length EMPID2 $9.;
EMPID2 = SUBSTR(cats('000000000',TRIM(EMPID)), Length(cats('000000000',TRIM(EMPID)))- 8, 9);
Drop EMPID
Rename EMPID2 = EMPID;
Run;
Proc Sort Data=EMPID_TABLE_ID;
BY EMPID
Run;
/**************************/
/*MACRO FOR CONTROL TOTALS*/
/**************************/
%MACRO CONTROL_TOTALS(DSN, VAR_LIST);
PROC SUMMARY DATA=&DSN. NWAY MISSING;
VAR &VAR_LIST.;
OUTPUT OUT=SUMM SUM=;
RUN;
%LET CNT = %EVAL(%SYSFUNC(LENGTH(%SYSFUNC(COMPBL("&VAR_LIST."))))-%SYSFUNC(LENGTH(%SYSFUNC(COMPRESS("&VAR_LIST."))))+1);
%PUT "COUNT OF VARIABLES: &CNT.";
DATA _NULL_;
SET SUMM;
FORMAT _FREQ_ COMMA20.;
FORMAT &VAR_LIST. COMMA30.2;
PUT @3 '----------------------------------------------------'/;
PUT @5 "CONTROL TOTAL AND TOTAL NUMBER OF RECORDS IN &DSN."/;
PUT @10 'TOTAL NUMBER OF RECORDS = ' _FREQ_/;
%DO I = 1 %TO &CNT.;
%LET VAR = %SCAN(&VAR_LIST.,&I.);
PUT @10 "TOTAL OF &VAR. = " &VAR./;
%END;
PUT @3 '----------------------------------------------------'/;
RUN;
%MEND;
%LET MONYR = SEP21;
%LET MONFLG = SEPTEMBER-2021;
/************************************************************/
/** MACRO TO READ IN EXPENDITURE FILES AND CREATE PERMANENT **/
/** DATA SET FOR EACH MONTH. **/
/************************************************************/
DATA GROUPE_FORMAT1_&monyr. (KEEP=EMPID CATID AI LEVEL YEARS FY MONTH CURR_MO_AMT NXT_MO_AMT CURMONODAYS NXTMONODAYS CURMONNOPMT NXTMONNOPMT NODAYSENTMH TRAINING_YRS CNTRY_CODE STATUS AIC ADSN SVC_COMP SOURCE FILE_MONTH_FLAG)
GROUPE_FORMAT3_&monyr. (KEEP=EMPID CATID AI LEVEL FY MONTH CURR_MO_AMT NXT_MO_AMT BAL_DUE ORG_DEBTAMT INDBTNS_TYP AMT_FORGVN CNTRY_CODE STATUS AIC ADSN SVC_COMP SOURCE FILE_MONTH_FLAG)
GROUPE_FORMAT4_&monyr. (KEEP=EMPID CATID AI LEVEL FY MONTH AMT_OF_COLL CNTRY_CODE STATUS AIC ADSN SVC_COMP SOURCE FILE_MONTH_FLAG)
GROUPE_FORMAT6_&monyr. (KEEP=EMPID CATID AI LEVEL YEARS FY MONTH CURR_MO_AMT NXT_MO_AMT NOMTHPURBND MTHBNDISD BNS_SR_TYP CNTRY_CODE STATUS AIC ADSN SVC_COMP SOURCE FILE_MONTH_FLAG)
;
INFILE "/opt/app/sas/WS_files/EXPEAPR&monyr._final.txt";
LENGTH SOURCE $15.;
SOURCE = "EXPENDITURES";
LENGTH FILE_MONTH_FLAG $15.;
FILE_MONTH_FLAG = "&monflg.";
INPUT @1 EMPID $9.
@10 CATID $2. @;
IF CATID IN ("AW","RX","R0","D1","D1","D2","D3","D4","D5","D6","D7","D8","D1","D3","D5","D7","X9") THEN
DO;
INPUT @13 AI $1.
@14 LEVEL $3.
@17 YEARS $3.
@20 FY $1.
@21 MONTH $3.
@24 CURR_MO_AMT $7.
@31 NXT_MO_AMT $7.
@38 CURMONODAYS $3.
@41 NXTMONODAYS $3.
@44 CURMONNOPMT $1.
@45 NXTMONNOPMT $1.
@46 NODAYSENTMH $3.
@53 TRAINING_YRS $3.
@79 CNTRY_CODE $2.
@81 STATUS $1.
@82 AIC $4.
@86 ADSN $4.
@90 SVC_COMP $1.
;
OUTPUT GROUPE_FORMAT1_&monyr.;
END;
ELSE IF CATID IN ("E1","E2","E3","G1","K1","M1","P1","Q1","Q2","R1","S1","V1","V2","W1") THEN
DO;
INPUT @13 AI $1.
@14 LEVEL $3.
@17 FILLER $2.
@19 FY $1.
@20 MONTH $3.
@23 CURR_MO_AMT $9.
@32 NXT_MO_AMT $9.
@41 BAL_DUE $9.
@50 ORG_DEBTAMT $9.
@59 INDBTNS_TYP $1.
@60 AMT_FORGVN $9.
@88 CNTRY_CODE $2.
@90 STATUS $1.
@91 AIC $4.
@95 ADSN $4.
@99 SVC_COMP $1.
;
OUTPUT GROUPE_FORMAT3_&monyr.;
END;
ELSE IF CATID IN ("A4","A5","CA","C1","CC","C3","C5","E1","P1") THEN
DO;
INPUT @13 AI $1.
@14 LEVEL $3.
@20 FY $1.
@21 MONTH $3.
@24 AMT_OF_COLL $9.
@73 CNTRY_CODE $2.
@75 STATUS $1.
@76 AIC $4.
@80 ADSN $4.
@84 SVC_COMP $1.
;
OUTPUT GROUPE_FORMAT4_&monyr.;
END;
ELSE IF CATID IN ("AC","AD","AF","AH","AI","AJ","AL","AN","AS","AT","AV","A1","SN") THEN
DO;
INPUT @13 AI $1.
@14 LEVEL $3.
@19 FY $1.
@20 MONTH $3.
@23 CURR_MO_AMT $7.
@30 NXT_MO_AMT $7.
@39 NOMTHPURBND $3.
@42 MTHBNDISD $3.
@45 BNS_SR_TYP $1.
@76 CNTRY_CODE $2.
@78 STATUS $1.
@79 AIC $4.
@83 ADSN $4.
@87 SVC_COMP $1.
;
OUTPUT GROUPE_FORMAT6_&monyr.;
END;
RUN;
/** GROUP E - FORMAT 1 **/
DATA GROUPE_FORMAT1_&monyr._MOD;
SET GROUPE_FORMAT1_&monyr.;
LEVEL_NUM = LEVEL * 1;
CUR_AMOUNT = CURR_MO_AMT * .01;
IF AI IN ("0","1") THEN DO;
CURR_AMOUNT = CUR_AMOUNT*1;
END;
ELSE DO;
CURR_AMOUNT = -(CUR_AMOUNT)*1;
END;
LENGTH EMP_LEVEL $8.;
IF LEVEL_NUM < 26 THEN EMP_LEVEL = "STAFF";
ELSE IF LEVEL_NUM < 40 THEN EMP_LEVEL = "MANAGEMENT";
ELSE IF LEVEL_NUM < 55 THEN EMP_LEVEL = "EXECUTIVE";
LENGTH APPN $4.;
IF SVC_COMP IN("S","M","E") THEN APPN = "1343";
IF SVC_COMP IN("T") THEN APPN = "1657";
/* KEEP EMPID CATID LEVEL ADSN FY MONTH FILE_MONTH_FLAG CURR_AMOUNT SOURCE EMP_LEVEL;*/
RUN;
/** GROUP E - FORMAT 3 **/
DATA GROUPE_FORMAT3_&monyr._MOD;
SET GROUPE_FORMAT3_&monyr.;
LEVEL_NUM = GRADE * 1;
CUR_AMOUNT = CURR_MO_AMT * .01;
IF AI IN ("0","1") THEN DO;
CURR_AMOUNT = CUR_AMOUNT*1;
END;
ELSE DO;
CURR_AMOUNT = -(CUR_AMOUNT)*1;
END;
IF CATID IN("D1","D2") THEN DO CURR_AMOUNT = CURR_AMOUNT * 100;
END;
LENGTH EMP_LEVEL $8.;
IF LEVEL_NUM < 26 THEN EMP_LEVEL = "STAFF";
ELSE IF LEVEL_NUM < 40 THEN MEMBER_GRADE = "MANAGEMENT";
ELSE IF LEVEL_NUM < 55 THEN MEMBER_GRADE = "EXECUTIVE";
LENGTH APPN $4.;
IF SVC_COMP IN("S","M","E") THEN APPN = "1343";
IF SVC_COMP IN("T") THEN APPN = "1657";
/* KEEP EMPID CATID LEVEL ADSN FY MONTH FILE_MONTH_FLAG CURR_AMOUNT SOURCE EMP_LEVEL;*/
RUN;
/** GROUP E - FORMAT 4 **/
DATA GROUPE_FORMAT4_&monyr._MOD;
SET GROUPE_FORMAT4_&monyr.;
LEVEL_NUM = GRADE * 1;
CUR_AMOUNT = AMT_OF_COLL * .01;
IF AI IN ("0","1") THEN DO;
CURR_AMOUNT = CUR_AMOUNT*1;
END;
ELSE DO;
CURR_AMOUNT = -(CUR_AMOUNT)*1;
LENGTH EMP_LEVEL $8.;
IF LEVEL_NUM < 26 THEN EMP_LEVEL = "STAFF";
ELSE IF LEVEL_NUM < 40 THEN EMP_LEVEL = "MANAGEMENT";
ELSE IF LEVEL_NUM < 55 THEN EMP_LEVEL = "EXECUTIVE";
LENGTH APPN $4.;
IF SVC_COMP IN("S","M","E") THEN APPN = "1343";
IF SVC_COMP IN("T") THEN APPN = "1657";
END;
/* KEEP EMPID CATID LEVEL ADSN FY MONTH FILE_MONTH_FLAG CURR_AMOUNT SOURCE EMP_LEVEL;*/
RUN;
/** GROUP E - FORMAT 6 **/
DATA GROUPE_FORMAT6_&monyr._MOD;
SET GROUPE_FORMAT6_&monyr.;
LEVEL_NUM = GRADE * 1;
CUR_AMOUNT = CURR_MO_AMT * .01;
IF AI IN ("0","1") THEN DO;
CURR_AMOUNT = CUR_AMOUNT*1;
END;
ELSE DO;
CURR_AMOUNT = -(CUR_AMOUNT)*1;
LENGTH MEMBER_GRADE $8.;
IF LEVEL_NUM < 26 THEN EMP_LEVEL = "STAFF";
ELSE IF LEVEL_NUM < 40 THEN EMP_LEVEL = "MANAGEMENT";
ELSE IF LEVEL_NUM < 55 THEN EMP_LEVEL = "EXECUTIVE";
LENGTH APPN $4.;
IF SVC_COMP IN("S","M","E") THEN APPN = "1343";
IF SVC_COMP IN("T") THEN APPN = "1657";
END;
/* KEEP EMPID CATID LEVEL ADSN FY MONTH FILE_MONTH_FLAG CURR_AMOUNT SOURCE EMP_LEVEL;*/
RUN;
Data DTA.Expenditures_&monyr.;
Set GROUPE_FORMAT1_&monyr._MOD
GROUPE_FORMAT3_&monyr._MOD
GROUPE_FORMAT4_&monyr._MOD
GROUPE_FORMAT6_&monyr._MOD;
Run;
%Macro Detail_EMP_Exp();
Proc Sort Data= DTA.Expenditures_&monyr.;
BY EMPID;
Run;
Data Detail_EMP_Exp;
Merge DTA.Expenditures_&monyr. (in=A) EMPID_TABLE_ID (in=B);
BY EMPID;
If B;
Run;
Proc Sort Data=Detail_EMP_Exp;
By CATID;
Run;
Data Detail_EMP_Exp_mod;
Merge Detail_EMP_Exp (in=A) SAF_TABLE (in=B);
BY CATID;
If A;
Run;
Proc Sort Data=Detail_EMP_Exp_mod;
By EMPID CATID FY MONTH EMPID;
Run;
Proc Export Data=Detail_EMP_Exp_mod
Outfile="/opt/app/sas/WS_files/FY22 Emp Samples Expenditures.csv"
DBMS=CSV REPLACE;
Run;
%MEND;
%Detail_EMP_Exp;
Click image to register for webinar
