data IMSHR.EMPOWER;
SET IMSHR.EMPOWER12212016;

format StateCd $3.;
StateCD = riskstatecd;


IF PRODUCT = 'STDC' or PRODUCT = 'LTDC';
IF nonproviderpaymenttype = 'CLAIMANT' or nonproviderpaymenttype = 'EMPLOYER';

/* THESE VARIABLES ARE TO BE CHANGED LATER ONCE THE RECOVERY AND STOP PAYMENT INFORMATION IS UPDATED IN THE LAKE */


log_chk_adj_ben_gross_amt = 0;
rcov_adj_ben_gross_amt = 0;
log_chk_state_wage = 0;
rcov_state_wage = 0;
log_chk_state_tax_wthd_amt = 0;
rcov_state_tax_withd_amt = 0;
rcov_state_wage = 0;
log_chk_adj_ben_gross_amt = 0;
rocv_adj_ben_gross_amt = 0;
log_chk_fed_wage = 0;
rocv_fed_wage = 0;
FEIN = '04697603';
TAXABLEWAGE_ST_EXCL = 0;
TAXAMOUNT_ST_EXCL = 0;
GROSSWAGE_FED_FUTA = 0;
TAXABLEWAGE_FED_FUTA = 0;
TAXAMOUNT_FED_FUTA = 0;
GROSSWAGE_FED_SS = 0;
GROSSWAGE_FED_MEDI = 0;
TAXAMOUNT_FED_EXCL = 0;
TAXABLEWAGE_FED_EXCL = 0;
log_chk_fed_tax_wthd_amt = 0;
rcocv_fed_tax_wthd_amt = 0;
log_chk_ssoai_wage = 0;
rcov_ssoai_wage = 0;
log_chk_ssoai_tax_wthd = 0;
rcov_ssoai_tax_wthd_amt = 0;
log_chk_medcr_wage = 0;
rcov_medcr_wage = 0;
log_chk_medcr_tax_wthd_amt = 0;
rocv_medcr_tax_wthd_amt = 0;




/* THE BELOW CALCULATIONS ARE FOR WAGE RECORDS FOR STATE TAX RECORDS */

GROSSWAGE_ST_WAGEREC = ADJGROSSBENEFITAMOUNT + log_chk_adj_ben_gross_amt + rcov_adj_ben_gross_amt;
TAXABLEWAGE_ST_WAGEREC = STATETAXABLEWAGES + log_chk_state_wage + rcov_state_wage;
TAXAMOUNT_ST = STATESTAXAMOUNT + log_chk_state_tax_wthd_amt - rcov_state_tax_withd_amt;
GROSS_ST_EXCL = ADJGROSSBENEFITAMOUNT + log_chk_adj_ben_gross_amt + rcov_adj_ben_gross_amt - STATETAXABLEWAGE + rcov_state_wage;

/*THE BELOW CALCULATIONS ARE FOR FEDERAL TAX RECORDS*/

GROSSWAGE_FED = ADJGROSSBENEFITAMOUNT + log_chk_adj_ben_gross_amt - rocv_adj_ben_gross_amt;
TAXABLEWAGE_FED = FEDERALTAXABLEWAGES + log_chk_fed_wage  -  rocv_fed_wage;
TAXAMOUNT_FED_IT = FEDERALEXCLUDABLETAXABLEWAGES + log_chk_fed_tax_wthd_amt - rcocv_fed_tax_wthd_amt;
TAXABLEWAGE_FED_SS = OASDITAXABLEWAGES + log_chk_ssoai_wage - rcov_ssoai_wage;
TAXAMOUNT_FED_SSEE = FICASOCIALSECURITYTAXAMOUNT + log_chk_ssoai_tax_wthd - rcov_ssoai_tax_wthd_amt;
TAXAMOUNT_FED_SSER = FICASOCIALSECURITYTAXAMOUNT + log_chk_ssoai_tax_wthd - rcov_ssoai_tax_wthd_amt;
TAXABLEWAGE_FED_MEDI = MEDICARETAXABLEWAGES  + log_chk_medcr_wage - rcov_medcr_wage;
TAXAMOUNT_FED_MEDEE = FICAMEDICARETAXAMOUNT + log_chk_medcr_tax_wthd_amt - rocv_medcr_tax_wthd_amt;
TAXAMOUNT_FED_MEDER = FICAMEDICARETAXAMOUNT + log_chk_medcr_tax_wthd_amt - rocv_medcr_tax_wthd_amt;
GROSSAMOUNT_FED_EXCL = ADJGROSSBENEFITAMOUNT + log_chk_adj_ben_gross_amt - rocv_adj_ben_gross_amt - FEDERALTAXABLEWAGES - log_chk_fed_wage + rocv_fed_wage;


RUN;

/* summarization of state records * this will change when the query is updated to reflect coveragecd as 'Product' instead of productcd as 'Claim_Product*/

PROC SUMMARY DATA= IMSHR.EMPOWER NWAY;
class STATECD PRODUCT; 
var GROSSWAGE_ST_WAGEREC TAXABLEWAGE_ST_WAGEREC TAXAMOUNT_ST GROSS_ST_EXCL TAXABLEWAGE_ST_EXCL TAXAMOUNT_ST_EXCL; 
output OUT= IMSHR.StateTaxTable SUM=;

RUN; 
/* summarization of federal records */

PROC SUMMARY DATA= IMSHR.EMPOWER NWAY;
class PRODUCT;
var GROSSWAGE_FED_FUTA TAXAMOUNT_FED_FUTA TAXABLEWAGE_FED_FUTA GROSSWAGE_FED TAXABLEWAGE_FED TAXAMOUNT_FED_IT GROSSWAGE_FED_SS TAXABLEWAGE_FED_SS TAXAMOUNT_FED_SSEE
TAXAMOUNT_FED_SSER GROSSWAGE_FED_MEDI TAXABLEWAGE_FED_MEDI TAXAMOUNT_FED_MEDEE TAXAMOUNT_FED_MEDER GROSSAMOUNT_FED_EXCL TAXABLEWAGE_FED_EXCL
TAXAMOUNT_FED_EXCL TAXAMOUNT_FED_FUTA;
output OUT= IMSHR.FederalTaxTable SUM=;

RUN;

/* creating two tables for the different types of state wage records (IT and EXCL) */
DATA IMSHR.StateW1;
set IMSHR.STATETAXTABLE;

TYPE = "W1";
Record = "State";
KEEP TYPE Record StateCd product Grosswage_st_wagerec taxablewage_st_wagerec;

RUN; 

DATA IMSHR.StateW2;
set IMSHR.STATETAXTABLE;

TYPE = "W2";
Record = "State";
KEEP  TYPE Record statecd product gross_st_excl taxablewage_st_excl;

RUN; 

data IMSHR.StateW1_v2;
set IMSHR.StateW1;

RENAME GROSSWAGE_ST_WAGEREC = Wage;
RENAME taxablewage_st_wagerec = Tax;

RUN;

data IMSHR.StateW2_v2;
set IMSHR.StateW2;

RENAME GROSS_ST_EXCL = Wage;
RENAME TAXABLEWAGE_ST_EXCL = Tax;

RUN;

/*appending the two versions of wage records*/

data IMSHR.StateW;
set IMSHR.StateW1_V2 IMSHR.StateW2_V2;
RUN;

/*creation of federal records*/

/* W1 = FIRST FEDERAL WAGE RECORD (FUTA)*/

DATA IMSHR.Federal_W1;
set IMSHR.FEDERALTAXTABLE;

TYPE = "W1";
Record = "Fed";
statecd = 'All';

KEEP TYPE statecd product Record GROSSWAGE_FED_FUTA TAXABLEWAGE_FED_FUTA;

RUN;

data IMSHR.Federal_W1_V2;
set IMSHR.Federal_W1;

RENAME GROSSWAGE_FED_FUTA = Wage;
RENAME TAXABLEWAGE_FED_FUTA = Tax;

RUN;

/*W2 = SECOND FEDERAL WAGE RECORD (FIT) */

DATA IMSHR.Federal_W2;
set IMSHR.FEDERALTAXTABLE;

TYPE = "W2";
Record = "Fed";
statecd = 'All';

KEEP TYPE statecd product Record GROSSWAGE_FED TAXABLEWAGE_FED;

RUN;

data IMSHR.Federal_W2_V2;
set IMSHR.Federal_W2;

RENAME GROSSWAGE_FED = Wage;
RENAME TAXABLEWAGE_FED = Tax;

RUN;
/*W3 = THIRD FEDERAL WAGE RECORD (SOCIAL SECURITY) */

DATA IMSHR.Federal_W3;
set IMSHR.FEDERALTAXTABLE;

TYPE = "W3";
Record = "Fed";
statecd = 'All';

KEEP TYPE statecd product Record GROSSWAGE_FED_SS TAXABLEWAGE_FED_SS;

RUN;

DATA IMSHR.Federal_W3_V2;
set IMSHR.Federal_W3;

RENAME GROSSWAGE_FED_SS = Wage;
RENAME TAXABLEWAGE_FED_SS = Tax;

RUN;


/* W4 = FOURTH FEDERAL WAGE RECORD (MEDICARE)*/

DATA IMSHR.Federal_W4;
set IMSHR.FederalTaxTable;

TYPE = "W4";
Record = "Fed";
statecd = 'All';

KEEP TYPE statecd product Record GROSSWAGE_FED_MEDI TAXABLEWAGE_FED_MEDI;

RUN;

DATA IMSHR.Federal_W4_V2;
set IMSHR.Federal_W4;

RENAME GROSSWAGE_FED_MEDI = Wage;
RENAME TAXABLEWAGE_FED_MEDI = Tax;

RUN;

/* W5 = FIFTH FEDERAL WAGE RECORD (FED EXCLUSION) */

DATA IMSHR.Federal_W5;
set IMSHR.FEDERALTAXTABLE;

TYPE = "W5";
Record = "Fed";
statecd = "All";

KEEP TYPE statecd product Record GROSSAMOUNT_FED_EXCL TAXABLEWAGE_FED_EXCL;

RUN;

DATA IMSHR.Federal_W5_V2;
set IMSHR.Federal_W5;

RENAME GROSSAMOUNT_FED_EXCL = Wage;
RENAME TAXABLEWAGE_FED_EXCL = Tax;

RUN;

/*appending the FIVE versions of federal records*/

data IMSHR.FederalW;
set IMSHR.Federal_W1_V2 IMSHR.federal_w2_v2 IMSHR.federal_w3_v2 IMSHR.federal_w4_v2 IMSHR.federal_w5_v2;
RUN;


/*CREATION OF STATE TAX RECORDS */

DATA IMSHR.StateT1;
set IMSHR.STATETAXTABLE;

TYPE = "T1";
Record = "State";
TranslationCode=cat(0,statecd,"IT");


KEEP TYPE Record statecd product TAXAMOUNT_ST translationcode;
RUN; 

DATA IMSHR.StateT2;
set IMSHR.STATETAXTABLE;

TYPE = "T2";
Record = "State";
TranslationCode=cat(0,statecd,"EXCL");

KEEP  TYPE Record statecd product TAXAMOUNT_ST_EXCL TranslationCode;

RUN; 

data IMSHR.StateT1_v2;
set IMSHR.StateT1;

RENAME TAXAMOUNT_ST = Amount;

RUN;

data IMSHR.StateT2_v2;
set IMSHR.StateT2;

RENAME TAXAMOUNT_ST_EXCL = Amount;

RUN;

/*appending the two versions of STATE TAX records*/

data IMSHR.StateT;
set IMSHR.StateT1_V2 IMSHR.StateT2_V2;
RUN;

/* creation of Federal Tax Records */

/*t1 = FUTA TAX */

DATA IMSHR.FederalT1;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T1';
Record = 'Fed';
TranslationCode = '0940FUTA';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_FUTA TranslationCode;

RUN;

DATA IMSHR.FederalT1_V2;
set IMSHR.FederalT1;

RENAME TAXAMOUNT_FED_FUTA = Amount;

RUN;


/*t2 = FIT TAX */
DATA IMSHR.FederalT2;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T2';
Record = 'Fed';
TranslationCode = '0941FIT';
statecd = 'll';

KEEP TYPE Record statecd product TAXAMOUNT_FED_IT TranslationCode;

RUN;

DATA IMSHR.FederalT2_V2;
set IMSHR.FederalT2;

RENAME TAXAMOUNT_FED_IT = Amount;

RUN;

/*t3 = SocialSecurity EE TAX */
DATA IMSHR.FederalT3;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T3';
Record = 'Fed';
TranslationCode = '0941OASDIEE';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_SSEE TranslationCode;

RUN;

DATA IMSHR.FederalT3_V2;
set IMSHR.FederalT3;

RENAME TAXAMOUNT_FED_SSEE = Amount;

RUN;

/*t4 = SocialSecurity ER TAX */
DATA IMSHR.FederalT4;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T4';
Record = 'Fed';
TranslationCode = '0941OASDIER';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_SSER TranslationCode;

RUN;

DATA IMSHR.FederalT4_V2;
set IMSHR.FederalT4;

RENAME TAXAMOUNT_FED_SSER = Amount;

RUN;

/*t5 = Medicare EE TAX */
DATA IMSHR.FederalT5;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T5';
Record = 'Fed';
TranslationCode = '0941MEDEE';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_MEDEE TranslationCode;

RUN;

DATA IMSHR.FederalT5_V2;
set IMSHR.FederalT5;

RENAME TAXAMOUNT_FED_MEDEE = Amount;

RUN;

/*t6 = Medicare ER TAX */
DATA IMSHR.FederalT6;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T6';
Record = 'Fed';
TranslationCode = '0941MEDER';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_MEDER TranslationCode;

RUN;

DATA IMSHR.FederalT6_V2;
set IMSHR.FederalT6;

RENAME TAXAMOUNT_FED_MEDER = Amount;

RUN;

/*t7 = FEDERAL EXCL TAX */
DATA IMSHR.FederalT7;
set IMSHR.FEDERALTAXTABLE;

TYPE = 'T7';
Record = 'Fed';
TranslationCode = '0941EXCL';
statecd = 'All';

KEEP TYPE Record statecd product TAXAMOUNT_FED_EXCL TranslationCode;

RUN;

DATA IMSHR.FederalT7_V2;
set IMSHR.FederalT7;

RENAME TAXAMOUNT_FED_EXCL = Amount;

RUN;
/*appending the seven versions of FED TAX records*/

data IMSHR.FederalT;
set IMSHR.FederalT1_v2 IMSHR.FederalT2_v2 IMSHR.federalT3_v2 IMSHR.federalT4_v2 IMSHR.federalT5_v2 IMSHR.federalT6_v2 IMSHR.federalT7_v2;
RUN;


/*creation of header table*/

DATA IMSHR.Header;
set IMSHR.EMPOWER IMSHR.FEDERALT IMSHR.STATET;

TYPE = "H";
Record = 'Header';
PayrollID = 'ET';
UnitID = 561 or 562;
IF product = 'STDC' THEN UnitID = 562;
IF product = 'LTDC' THEN UnitID = 561;

KEEP TYPE FEIN UnitID PayrollID statecd checkprinteddate product TranslationCode;
 
run; 

/*creation of master table which will include the STATEW, STATET, FEDERALW, FEDERALT and HEADER tables */

DATA IMSHR.Master;
set IMSHR.STATEW IMSHR.STATET IMSHR.FEDERALW IMSHR.FEDERALT IMSHR.HEADER;

KEEP TYPE FEIN AMOUNT product statecd PayrollID Wage Tax UnitID checkprinteddate TranslationCode;

RUN;

/*beginning the formatting of the output text file to be sent over to Josh Hersey */

DATA IMSHR.Master_V2;
set IMSHR.Master;

TYPESIMP = substr(TYPE,1,1);
PAYDATE = DATEPART(checkprinteddate);
format PAYDATE MMDDYY10.;

run;

/* PROC SORT function testing */

proc sort data=IMSHR.Master_V2 OUT=IMSHR.Master_v3 (keep=typesimp fein amount payrollid wage unitid paydate tax translationcode);
BY product StateCd;
run; 

/*SORTING THE DATA IN THE ORDER THAT IT APPEAR IN THE REPORT*/

Data IMSHR.MasterOrdered;
Retain typesimp fein amount payrollid wage unitid paydate tax translationcode;
Set IMSHR.Master_V3;
Run; 

/* USING AN ODS STATEMENT TO FORMAT THE TEXT FILE OUTPUT */

ods listing close;
ods escapechar = '^';
ods noproctitle;

options nodate number;
footnote;

ODS RTF FILE = '\\Client\C$\Users\n0294503\Documents\Empower' 
bodytitle 
startpage = no
style = journal;
options papersize = A4 
orientation = portrait;

title1 /*bold*/ /*italic*/ font = 'Times New Roman'  height = 12pt justify = center underlin = 0 color = black bcolor = white 'Table 1 name';
footnote1 /*bold*/ /*italic*/ font = 'Times New Roman'  height = 9pt justify = center underlin = 0 color = black bcolor = white 'Note: Created on January 2012';
 


/* ignore this for now
retain TYPE TYPESIMP FEIN Amount PayrollID Wage UnitID PAYDATE Tax TranslationCode;
set Master;
keep TYPE TYPESIMP FEIN Amount PayrollID Wage UnitID PAYDATE Tax TranslationCode;

run; */