Desktop productivity for business analysts and programmers

Help needed with how to get program to format ....

Reply
Frequent Contributor
Posts: 82

Help needed with how to get program to format ....

[ Edited ]

 

I'm trying to get my program to get the summary output to look like this:

Branch of ServiceParent Base NameTotal Approved BH Referrals / AuthorizationsTotal BH Care Claims Received since Ref/AuthPercent of BH Care Claims Received since Ref/AuthTotal Beneficiaries Without BH Care Claims since Ref/AuthPercent of Bene Without BH Care Claims since Ref/Auth
       
       
       
       
       
 Total DMIS 001:     


%GLOBAL FileNameUserID; %GLOBAL FileNameDate; %GLOBAL FileNameEmail; %LET FileNameUserID =fs111111; %LET FileNameDate = 201801; %LET FileNameEmail = M290BH; %LET TEST = &cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate.; %LET NAME = M290; %PUT &TEST; LIBNAME &NAME "&cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate."; **** RUN DATE: 01/08/2018; ** UPDATE GLOBAL DATES HERE; %LET F_DOS1='2018-01-01'; *** BEG DOS FOR MMS; %LET L_DOS1='2018-01-31'; *** END DOS FOR MMS; %LET F_DOS2='2018-01-01'; *** BEG DOS FOR CLAIMS; %LET L_DOS2='2018-01-31'; *** END DOS AND PROCESS END DT FOR CLAIMS; ** MMS PULL 1; proc sql; CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.com"); CREATE TABLE &NAME..MMS AS SELECT * FROM CONNECTION TO tera (SELECT MMS_AUTHRZD_MBR.SPNSR_BRNCH_OF_SRVC_CD, MMS_AUTHRZD_MBR.SPNSR_BRNCH_OF_SRVC_DESC, MMS_AUTHRZTN.AUTHRZTN_RFRNC_NUM, MMS_AUTHRZTN.AUTHRZTN_1_NUM, MMS_AUTHRZTN.AUTHRZTN_2_NUM, MMS_AUTHRZTN.FIRST_CLOSED_DT, MMS_AUTHRZD_MBR.PCM_DMIS_ID, MMS_AUTHRZD_MBR.SPNSR_STUS_CD, MMS_AUTHRZD_MBR.BENE_ROLE_CD, MMS_AUTHRZTN.PRMRY_DGNS_CD, MMS_AUTHRZTN.PRMRY_DGNS_DESC, MMS_AUTHRZD_MBR.BIRTH_DT, MMS_AUTHRZD_MBR.SPNSR_SSN, A_ZAA_AUTHMEMBERZIP.EMSM_CD, DMIS_REPORT.PARNT_DMIS_ID, MMS_MTF_MATCH_DTL_HSTRY.MTF_NAME FROM MMS_MTF_MATCH_DTL_HSTRY RIGHT OUTER JOIN MMS_AUTHRZTN ON (MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID=MMS_MTF_MATCH_DTL_HSTRY.AUTHRZTN_UNIQ_ID) INNER JOIN MMS_AUTHRZD_MBR ON (MMS_AUTHRZD_MBR.AUTHRZTN_UNIQ_ID=MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID) LEFT OUTER JOIN ZIP_AREA_ASGNMT A_ZAA_AUTHMEMBERZIP ON (A_ZAA_AUTHMEMBERZIP.ZIP_CD=MMS_AUTHRZD_MBR.ZIP AND CURRENT_DATE BETWEEN A_ZAA_AUTHMEMBERZIP.EFCTV_DT and A_ZAA_AUTHMEMBERZIP.TRMNTN_DT) LEFT OUTER JOIN DMIS_REPORT ON (MMS_AUTHRZD_MBR.PCM_DMIS_ID=DMIS_REPORT.DMIS_ID) WHERE MMS_AUTHRZTN.FIRST_CLOSED_DT BETWEEN &F_DOS1 AND &L_DOS1 AND MMS_AUTHRZTN.AUTHRZTN_CLOSED_FLAG='Y' AND MMS_AUTHRZTN.CASE_TYPE_CD IN ('OPBH') AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID='A'); %PUT &SQLXMSG &SQLXRC; ** MMS PULL 2; proc sql; CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.com"); CREATE TABLE &NAME..MMSP2 AS SELECT * FROM CONNECTION TO tera (SELECT MMS_AUTHRZTN.AUTHRZTN_RFRNC_NUM, MMS_AUTHRZTN.AUTHRZTN_1_NUM, MMS_AUTHRZTN.AUTHRZTN_2_NUM, MMS_AUTHRZD_MBR.SPNSR_SSN, MMS_AUTHRZD_MBR.BIRTH_DT, MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_FROM_DT, MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_TO_DT, MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_UNIQ_ID, MMS_AUTHRZTN.PRMRY_DGNS_CD, MMS_AUTHRZD_SRVC.AUTHRZD_STRTG_PRCDR_CD, MMS_AUTHRZD_SRVC.AUTHRZD_ENDG_PRCDR_CD, MMS_AUTHRZTN.FIRST_CLOSED_DT, MMS_AUTHRZD_SRVC.AUTHRZD_QTY, MMS_AUTHRZTN.AUTHRZTN_PRFL_CD, MMS_AUTHRZD_PRVDR.PRMRY_SPCLTY_NAME FROM MMS_AUTHRZD_PRVDR RIGHT OUTER JOIN MMS_AUTHRZD_PRVDR_SRVC_ASCTN ON (MMS_AUTHRZD_PRVDR.AUTHRZTN_UNIQ_ID=MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZTN_UNIQ_ID AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_UNIQ_ID=MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_UNIQ_ID AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_ROLE_ID=MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_ROLE_ID AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID=MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_TYPE_ID) RIGHT OUTER JOIN MMS_AUTHRZD_SRVC ON (MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_UNIQ_ID=MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_SRVC_UNIQ_ID) INNER JOIN MMS_AUTHRZTN ON (MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID=MMS_AUTHRZD_SRVC.AUTHRZTN_UNIQ_ID) INNER JOIN MMS_AUTHRZD_MBR ON (MMS_AUTHRZD_MBR.AUTHRZTN_UNIQ_ID=MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID) WHERE MMS_AUTHRZTN.FIRST_CLOSED_DT BETWEEN &F_DOS1 AND &L_DOS1 AND MMS_AUTHRZTN.AUTHRZTN_CLOSED_FLAG='Y' AND MMS_AUTHRZTN.CASE_TYPE_CD IN ('OPBH') AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID='A'); %PUT &SQLXMSG &SQLXRC; *THERE WERE ? IN THE PCM DMIS, YET WERE SUBMITTED BY AN MTF; *SHOULD WE KEEP THESE OR ELIMINATE THEM?; *DECISION: KEEP THEM PER MARY AND LAWAYNA - 3/26/13; *THUS, NO RESTRICTION ON THE PCM DMIS IDENTIFIER FOR; *MTF AND CIV LINKED ON THE PULL ABOVE; *CLM DATA PULL CLAIMS MART FOR Q200 CDRL; proc sql; CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.com"); CREATE TABLE &NAME..CLM AS SELECT * FROM CONNECTION TO tera (SELECT CLM_HDR_DMNSN.CLM_BASE_NUM, CLM_LINE_FACT.CHCS_ORDR_NUM, DGNS_DMNSN.DGNS_1_CD, PLAN_ADMINR_DMNSN.MCS_SYS_IND, PRVDR_DMNSN.RNDRG_SPCLTY_CD, CLM_HDR_DMNSN.SPNSR_SSN, CLM_HDR_DMNSN.PTNT_BRTH_DT, CLNDR_TIME_DMNSN.CLNDR_DATE, PRCDR_DMNSN.DRVD_PRCDR_CD, DGNS_DMNSN.ICD_VRSN_IND FROM DGNS_DMNSN INNER JOIN CLM_LINE_FACT ON (DGNS_DMNSN.DGNS_DMNSN_ID=CLM_LINE_FACT.DGNS_DMNSN_ID) INNER JOIN CLM_HDR_DMNSN ON (CLM_LINE_FACT.CLM_HDR_ID=CLM_HDR_DMNSN.CLM_HDR_ID) INNER JOIN CLNDR_TIME_DMNSN ON (CLM_LINE_FACT.SRVC_DT_ID=CLNDR_TIME_DMNSN.CLNDR_TIME_ID) INNER JOIN PLAN_ADMINR_DMNSN ON (CLM_LINE_FACT.PLAN_ADMINR_ID=PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID) INNER JOIN PRCDR_DMNSN ON (CLM_LINE_FACT.PRCDR_DMNSN_ID=PRCDR_DMNSN.PRCDR_DMNSN_ID) INNER JOIN PRVDR_DMNSN ON (CLM_LINE_FACT.PRVDR_DMNSN_ID=PRVDR_DMNSN.PRVDR_DMNSN_ID) INNER JOIN CLNDR_TIME_DMNSN A_CLNDR_PRCS ON (A_CLNDR_PRCS.CLNDR_TIME_ID=CLM_LINE_FACT.CLM_PRCS_DT_ID) WHERE CLNDR_TIME_DMNSN.CLNDR_DATE BETWEEN &F_DOS2 AND &L_DOS2 AND CLM_LINE_FACT.LINE_ALOWD_AMT > 0 AND (PRCDR_DMNSN.DRVD_PRCDR_CD IN ('90791', '90792', '90785', '90832', '90833', '90834', '90836', '90837', '90838', '90839', '90840', '90846', '90847', '90849', '90853', '90863') OR PRCDR_DMNSN.DRVD_PRCDR_CD IN ('99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99241', '99242', '99243', '99244', '99245', '99341', '99342', '99343', '99344', '99345', '99346', '99347', '99348', '99349', '99350') AND PRVDR_DMNSN.RNDRG_SPCLTY_CD IN ('26', '27', '77', '62', '91')) AND A_CLNDR_PRCS.CLNDR_DATE <=&L_DOS2); %PUT &SQLXMSG &SQLXRC; /*BELOW KEEP ONLY FIRST 4 DIGITS DMIS IN AUTH NUMBERS; *AUTHS SUBMITTED BY MTF; *DELETE CLINIC/CENTER : SUBSTANCE ABUSE REHABILITATION FACILITY; *IF SERVICE QUANTITY IS 3 OR MORE;*/ DATA &NAME..MMS1; /*MAKE PERM*/ SET &NAME..MMS; ORDDMIS=SUBSTR(AUTHRZTN_1_NUM, 1, 4); IF SPNSR_STUS_CD IN ('A', 'N', 'V', 'J') AND BENE_ROLE_CD='BD' THEN BENECAT='ADSM '; ELSE IF SPNSR_STUS_CD IN ('A', 'N', 'V', 'J') AND BENE_ROLE_CD NE 'BD' THEN BENECAT='ADFM'; ELSE IF SPNSR_STUS_CD NOT IN ('A', 'N', 'V', 'J') THEN BENECAT='NADFM'; IF SUBSTR(AUTHRZTN_1_NUM, 5, 1)='-' THEN OUTPUT; RUN; ***** NOW DO THE SAME FOR MMS; DATA &NAME..MMSDEL (KEEP=AUTHRZTN_RFRNC_NUM); /*MAKE PERM*/ SET &NAME..MMSP2; WHERE AUTHRZTN_PRFL_CD IN ('P49' 'P50'); PRMRY_SPCLTY_NAME=UPCASE(PRMRY_SPCLTY_NAME); IF PRMRY_SPCLTY_NAME='SUBSTANCE USE REHAB FACILITY' AND AUTHRZD_QTY GE 3; RUN; PROC SORT DATA=&NAME..MMS1; /*MAKE PERMANENT*/ BY AUTHRZTN_RFRNC_NUM; RUN; PROC SORT DATA=&NAME..MMSDEL; /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; RUN; DATA &NAME..MMSDEL1; /*MAKE PERM*/ SET &NAME..MMSDEL; /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; IF FIRST.AUTHRZTN_RFRNC_NUM THEN OUTPUT; RUN; DATA &NAME..MMS2; /*MAKE PERM*/ MERGE &NAME..MMS1 (IN=IN1) &NAME..MMSDEL1 (IN=IN2); /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; IF IN1 AND NOT IN2; RUN; ** DETERMINE THE DUPLICATES; DATA &NAME..MMSP3A; /*MAKE PERM*/ SET &NAME..MMSP2; IF SUBSTR(AUTHRZTN_1_NUM, 5, 1)='-' THEN OUTPUT; RUN; DATA &NAME..MMSP3B (KEEP=DUPKEY AUTHRZTN_RFRNC_NUM FIRST_CLOSED_DT); /*MAKE PERM*/ SET &NAME..MMSP3A; /*MAKE PERM*/ DUPKEY=TRIM(SPNSR_SSN)!!TRIM(BIRTH_DT)!! TRIM(AUTHRZD_SRVC_FROM_DT)!!TRIM(AUTHRZD_SRVC_TO_DT)!! TRIM(AUTHRZD_PRVDR_UNIQ_ID)!!TRIM(PRMRY_DGNS_CD)!! TRIM(AUTHRZD_STRTG_PRCDR_CD)!!TRIM(AUTHRZD_ENDG_PRCDR_CD); RUN; PROC SORT NODUPKEY DATA=&NAME..MMSP3B; /*MAKE PERM*/ BY DUPKEY FIRST_CLOSED_DT; RUN; DATA &NAME..MMSP3K &NAME..MMSP3D (KEEP=AUTHRZTN_RFRNC_NUM); /*MAKE PERM*/ SET &NAME..MMSP3B; /*MAKE PERM*/ BY DUPKEY FIRST_CLOSED_DT; IF FIRST.DUPKEY THEN OUTPUT &NAME..MMSP3K; ELSE OUTPUT &NAME..MMSP3D; RUN; PROC SORT DATA=&NAME..MMSP3D; /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; RUN; DATA &NAME..MMSP3DA; /*MAKE PERM*/ SET &NAME..MMSP3D; /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; IF FIRST.AUTHRZTN_RFRNC_NUM THEN OUTPUT; RUN; PROC SORT DATA=&NAME..MMS2; /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; RUN; DATA &NAME..MMS2A; /*MAKE PERM*/ MERGE &NAME..MMS2 (IN=IN1) &NAME..MMSP3DA (IN=IN2); /*MAKE PERM*/ BY AUTHRZTN_RFRNC_NUM; IF IN1 AND NOT IN2; RUN; DATA &NAME..MMS2B (KEEP=AUTH_NUM /*MAKE PERM*/ AUTH_TRKG_NUM BENE_BIRTH_DT BENE_SPNSR_SSN BENECAT BRNCH_SRVC_CD BRNCH_SRVC_DESC FIRST_CLOSED_DT ORDDMIS PCM_DMIS_ID FLAG); SET &NAME..MMS2A; /*MAKE PERM*/ FLAG='MMS'; AUTH_NUM=AUTHRZTN_1_NUM; AUTH_TRKG_NUM=AUTHRZTN_RFRNC_NUM; BENE_BIRTH_DT=BIRTH_DT; BENE_SPNSR_SSN=SPNSR_SSN; BRNCH_SRVC_CD=SPNSR_BRNCH_OF_SRVC_CD; BRNCH_SRVC_DESC=SPNSR_BRNCH_OF_SRVC_DESC; RUN; DATA &NAME..MMSY; /*MAKE PERM*/ SET &NAME..MMS2B; /*MAKE PERM*/ RUN; PROC SORT NODUPKEY DATA=&NAME..MMSY; /*MAKE PERM*/ BY AUTH_TRKG_NUM; RUN; * NOW CREATE A BENE KEY FOR MATCHING; DATA &NAME..MMS3; /*MAKE PERM*/ SET &NAME..MMSY; /*MAKE PERM*/ KEY=TRIM(BENE_SPNSR_SSN)!!TRIM(BENE_BIRTH_DT); RUN; DATA &NAME..CLM1; /*MAKE PERM*/ SET &NAME..CLM; KEY=TRIM(SPNSR_SSN)!!TRIM(PTNT_BRTH_DT); RUN; PROC SORT DATA=&NAME..CLM1; /*MAKE PERM*/ BY CLM_BASE_NUM; RUN; DATA &NAME..CLM2; /*MAKE PERM*/ SET &NAME..CLM1; /*MAKE PERM*/ BY CLM_BASE_NUM; IF FIRST.CLM_BASE_NUM; RUN; PROC SQL; CREATE TABLE &NAME..MMSCLM AS /*MAKE PERM*/ SELECT A.*, B.* FROM &NAME..MMS3 AS A LEFT JOIN &NAME..CLM2 AS B /*MAKE PERM*/ ON (TRIM(LEFT(A.KEY)))=(TRIM(LEFT(B.KEY))); QUIT; DATA &NAME..MMSCL1; /*MAKE PERM*/ SET &NAME..MMSCLM; /*MAKE PERM*/ IF CLNDR_DATE GE FIRST_CLOSED_DT THEN CLMCNT=1; IF CLMCNT=1 THEN DO; IF ((ICD_VRSN_IND='09' OR MISSING(ICD_VRSN_IND)) AND DGNS_1_CD IN ('30300' '30301' '30302' '30303' '30390' '30391' '30392' '30393' '30500' '30501' '30502' '30503' '29181' '2910' '2911' '2912' '2914' '2915' '2913' '29182' '29189' '2919')) OR (ICD_VRSN_IND='10' AND DGNS_1_CD IN ('F1010' 'F10120' 'F10121' 'F10129' 'F1014' 'F10150' 'F10151' 'F10159' 'F10180' 'F10181' 'F10182' 'F10188' 'F1019' 'F1020' 'F1021' 'F10220' 'F10221' 'F10229' 'F10230' 'F10231' 'F10232' 'F10239' 'F1024' 'F10250' 'F10251' 'F10259' 'F1026' 'F1027' 'F10280' 'F10281' 'F10282' 'F10288' 'F1029' 'F10920' 'F10921' 'F10929' 'F1094' 'F10950' 'F10951' 'F10959' 'F1096' 'F1097' 'F10980' 'F10981' 'F10982' 'F10988' 'F1099')) THEN ALCOHOLFG='Y'; IF ((ICD_VRSN_IND='09' OR MISSING(ICD_VRSN_IND)) AND DGNS_1_CD IN ('30400' '30401' '30402' '30403' '30410' '30411' '30412' '30413' '30420' '30421' '30422' '30423' '30430' '30431' '30432' '30433' '30440' '30441' '30442' '30443' '30450' '30451' '30452' '30453' '30460' '30461' '30462' '30463' '30470' '30471' '30472' '30473' '30480' '30481' '30482' '30483' '30490' '30491' '30492' '30493' '30530' '30531' '30532' '30533' '30540' '30541' '30542' '30543' '30550' '30551' '30552' '30553' '30560' '30561' '30562' '30563' '30570' '30571' '30572' '30573' '30580' '30581' '30582' '30583' '30590' '30591' '30592' '30593' '2920' '29211' '29212' '2928' '29284' '29289' '2929' '30300' '30391' '30390' '30500' '3051' '30520' '30521' '30522' '30523' '29181' '2910' '2911' '2915' '2913' '29189' '2919' '2922' '29281' '29282' '29283' '29285')) OR (ICD_VRSN_IND='10' AND DGNS_1_CD IN ('F1010' 'F1094' 'F1124' 'F12188' 'F13150' 'F13921' 'F1419' 'F10120' 'F10950' 'F11250' 'F1219' 'F13151' 'F13929' 'F1420' 'F10121' 'F10951' 'F11251' 'F1220' 'F13159' 'F13930' 'F1421' 'F10129' 'F10959' 'F11259' 'F1221' 'F13180' 'F13931' 'F14220' 'F1014' 'F1096' 'F11281' 'F12220' 'F13181' 'F13932' 'F14221' 'F10150' 'F1097' 'F11282' 'F12221' 'F13182' 'F13939' 'F14222' 'F10151' 'F10980' 'F11288' 'F12222' 'F13188' 'F1394' 'F14229' 'F10159' 'F10981' 'F1129' 'F12229' 'F1319' 'F13950' 'F1423' 'F10180' 'F10988' 'F1190' 'F12250' 'F1320' 'F13951' 'F1424' 'F10181' 'F1099' 'F11920' 'F12251' 'F1321' 'F13959' 'F14250' 'F10188' 'F1110' 'F11921' 'F12259' 'F13220' 'F1396' 'F14251' 'F1019' 'F11120' 'F11922' 'F12280' 'F13221' 'F1397' 'F14259' 'F1020' 'F11121' 'F1193' 'F12288' 'F13229' 'F13980' 'F14280' 'F10220' 'F11122' 'F1194' 'F1229' 'F13230' 'F13981' 'F14281' 'F10221' 'F11129' 'F11950' 'F1290' 'F13231' 'F13982' 'F14282' 'F10229' 'F1114' 'F11951' 'F12920' 'F13232' 'F13988' 'F14288' 'F10230' 'F11150' 'F11959' 'F12921' 'F13239' 'F1399' 'F1429' 'F10231' 'F11151' 'F11981' 'F12922' 'F1324' 'F1410' 'F1490' 'F10232' 'F11159' 'F11982' 'F12929' 'F13250' 'F14120' 'F14920' 'F10239' 'F11181' 'F11988' 'F12950' 'F13251' 'F14121' 'F14921' 'F1024' 'F11182' 'F1199' 'F12951' 'F13259' 'F14122' 'F14922' 'F10250' 'F11188' 'F1210' 'F12959' 'F1326' 'F14129' 'F14929' 'F10251' 'F1119' 'F12120' 'F12980' 'F1327' 'F1414' 'F1494' 'F10259' 'F1120' 'F12121' 'F12988' 'F13280' 'F14150' 'F14950' 'F1026' 'F1121' 'F12122' 'F1299' 'F13281' 'F14151' 'F14951' 'F10280' 'F11220' 'F12129' 'F1310' 'F13282' 'F14159' 'F14959' 'F10281' 'F11221' 'F12150' 'F13120' 'F13288' 'F14180' 'F14980' 'F10288' 'F11222' 'F12151' 'F13121' 'F1329' 'F14181' 'F14981' 'F1029' 'F11229' 'F12159' 'F13129' 'F1390' 'F14182' 'F14982' 'F10921' 'F1123' 'F12180' 'F1314' 'F13920' 'F14188' 'F14988' 'F19920' 'F19921' 'F19922' 'F19929' 'F19930' 'F19931' 'F19932' 'F1997' 'F19980' 'F19981' 'F19982' 'F19988' 'F1999' 'F550' 'F1499' 'F1529' 'F1621' 'F17210' 'F18221' 'F19159' 'F1510' 'F1590' 'F16220' 'F17211' 'F18229' 'F1916' 'F15120' 'F15920' 'F16221' 'F17213' 'F1824' 'F1917' 'F15121' 'F15921' 'F16222' 'F17218' 'F18250' 'F19180' 'F15122' 'F15922' 'F16229' 'F17219' 'F18251' 'F19181' 'F15129' 'F15929' 'F1624' 'F17220' 'F18259' 'F19182' 'F1514' 'F1593' 'F16250' 'F17221' 'F1827' 'F19188' 'F15150' 'F1594' 'F16251' 'F17223' 'F18280' 'F1919' 'F15151' 'F15950' 'F16259' 'F17228' 'F18288' 'F1920' 'F15159' 'F15951' 'F16280' 'F17229' 'F1829' 'F1921' 'F15180' 'F15959' 'F16283' 'F17290' 'F1890' 'F19220' 'F15181' 'F15980' 'F16288' 'F17291' 'F18920' 'F19221' 'F15182' 'F15981' 'F1629' 'F17293' 'F18921' 'F19222' 'F15188' 'F15982' 'F1690' 'F17298' 'F18929' 'F19229' 'F1519' 'F15988' 'F16920' 'F17299' 'F1894' 'F19230' 'F1520' 'F1599' 'F16921' 'F1810' 'F18950' 'F19231' 'F1521' 'F1610' 'F16929' 'F18120' 'F18951' 'F19232' 'F15220' 'F16120' 'F1694' 'F18121' 'F18959' 'F19239' 'F15221' 'F16121' 'F16950' 'F18129' 'F1897' 'F1924' 'F15222' 'F16122' 'F16951' 'F1814' 'F18980' 'F19250' 'F15229' 'F16129' 'F16959' 'F18150' 'F18988' 'F19251' 'F1523' 'F1614' 'F16980' 'F18151' 'F1899' 'F19259' 'F1524' 'F16150' 'F16983' 'F18159' 'F1910' 'F1926' 'F15250' 'F16151' 'F16988' 'F1817' 'F19120' 'F1927' 'F15251' 'F16159' 'F1699' 'F18180' 'F19121' 'F19280' 'F15259' 'F16180' 'F17200' 'F18188' 'F19122' 'F19281' 'F15280' 'F16183' 'F17201' 'F1819' 'F19129' 'F19282' 'F15281' 'F16188' 'F17203' 'F1820' 'F1914' 'F19288' 'F15282' 'F1619' 'F17208' 'F1821' 'F19150' 'F1929' 'F15288' 'F1620' 'F17209' 'F18220' 'F19151' 'F1990' 'F19939' 'F1994' 'F19950' 'F19951' 'F19959' 'F1996' 'F551' 'F552' 'F553' 'F554' 'F558')) THEN SUBSTANFG='Y'; IF MCS_SYS_IND='I' THEN NETCNT=1; ELSE NONNETCNT=1; IF ALCOHOLFG='Y' THEN CLMALC=1; ELSE CLMALC=0; IF SUBSTANFG='Y' OR ALCOHOLFG='Y' THEN CLMSUB=1; ELSE CLMSUB=0; END; RUN; ** SUMMARIZE CLAIMS; PROC SUMMARY NWAY MISSING DATA=&NAME..MMSCL1; /*MAKE PERM*/ CLASS AUTH_TRKG_NUM; VAR CLMCNT NETCNT NONNETCNT CLMSUB CLMALC; ID BRNCH_SRVC_CD BRNCH_SRVC_DESC AUTH_NUM FIRST_CLOSED_DT PCM_DMIS_ID ORDDMIS BENECAT KEY FLAG; OUTPUT OUT=&NAME..MMSCL2 SUM=; PROC SORT DATA=&NAME..MMSCL2; BY KEY AUTH_TRKG_NUM; RUN; DATA OUTPUTA; SET &NAME..MMSCL2; DATETM1=DATETIME(); IF FLAG1 NE 2 THEN FLAG1=1; FLAG1+0; IF FLAG1=1 THEN PUT 'BRNCH_SRVC_CD' '~' 'BRNCH_SRVC_DESC' '~' 'AUTH_TRKG_NUM' '~' 'AUTH_NUM' '~' 'FIRST_CLOSED_DT' '~' 'PCM_DMIS_ID' '~' 'ORDDMIS' '~' 'BENECAT' '~' 'CLMCNT' '~' 'NETCNT' '~' 'NONNETCNT' '~' 'CLMSUB' '~' 'CLMALC' '~' 'FLAG' '~' DATETM1 DATETIME17.; FLAG1=2; PUT BRNCH_SRVC_CD +(-1) '~' BRNCH_SRVC_DESC +(-1) '~' AUTH_TRKG_NUM +(-1) '~' AUTH_NUM +(-1) '~' FIRST_CLOSED_DT +(-1) '~' PCM_DMIS_ID +(-1) '~' ORDDMIS +(-1) '~' BENECAT +(-1) '~' CLMCNT +(-1) '~' NETCNT +(-1) '~' NONNETCNT +(-1) '~' CLMSUB +(-1) '~' CLMALC +(-1) '~' FLAG +(-1) '~'; RETURN; * CODE FOR SUMMARY REPORT; DATA &NAME..MMSCL3; SET &NAME..MMSCL2; AUTHCNT=1; IF CLMCNT=0 OR MISSING(CLMCNT) THEN NOBHCLMCNT=1; IF CLMCNT>0 THEN BHCLMCNT=1; RUN; PROC SUMMARY NWAY MISSING DATA=&NAME..MMSCL3; VAR AUTHCNT NOBHCLMCNT BHCLMCNT NETCNT NONNETCNT CLMSUB CLMALC; OUTPUT OUT=&NAME..MMSCL4 SUM=; DATA &NAME..MMSCL5; SET &NAME..MMSCL4; PCBHREC=BHCLMCNT/AUTHCNT; PCBHNOTREC=NOBHCLMCNT/AUTHCNT; RUN; PROC TRANSPOSE DATA=&NAME..MMSCL5 OUT=&NAME..MMSCL6; VAR AUTHCNT NETCNT NONNETCNT NOBHCLMCNT PCBHREC PCBHNOTREC CLMSUB CLMALC; RUN; DATA OUTPUTB; SET &NAME..MMSCL6; DATETM1=DATETIME(); IF FLAG1 NE 2 THEN FLAG1=1; FLAG1+0; IF FLAG1=1 THEN PUT 'NAME' '~' 'CNT' '~' DATETM1 DATETIME17.; FLAG1=2; PUT _NAME_ +(-1) '~' COL1 +(-1) '~'; RETURN; ** SUMMARIZE BY BENE; PROC SUMMARY NWAY MISSING DATA=&NAME..MMSCL3; CLASS BENECAT; VAR AUTHCNT NOBHCLMCNT BHCLMCNT NETCNT NONNETCNT CLMSUB CLMALC; OUTPUT OUT=&NAME..MMSCL4A SUM=; DATA &NAME..MMSCL5A; SET &NAME..MMSCL4A; PCBHREC=BHCLMCNT/AUTHCNT; PCBHNOTREC=NOBHCLMCNT/AUTHCNT; RUN; DATA OUTPUTC; SET &NAME..MMSCL5A; DATETM1=DATETIME(); IF FLAG1 NE 2 THEN FLAG1=1; FLAG1+0; IF FLAG1=1 THEN PUT 'BENECAT' '~' 'AUTHCNT' '~' 'NETCNT' '~' 'NONNETCNT' '~' 'PCBHREC' '~' 'NOBHCLMCNT' '~' 'PCBHNOTREC' '~' 'CLMSUB' '~' 'CLMALC' '~' DATETM1 DATETIME17.; FLAG1=2; PUT BENECAT +(-1) '~' AUTHCNT +(-1) '~' NETCNT +(-1) '~' NONNETCNT +(-1) '~' PCBHREC +(-1) '~' NOBHCLMCNT +(-1) '~' PCBHNOTREC +(-1) '~' CLMSUB +(-1) '~' CLMALC +(-1) '~'; RETURN;

 

Super User
Posts: 22,875

Re: Help needed with how to get program to format according to Excel template attached

No attachment.
Super User
Posts: 22,875

Re: Help needed with how to get program to format according to Excel template attached

Your code has some content that could be considered confidential. 

Ask a Question
Discussion stats
  • 2 replies
  • 181 views
  • 1 like
  • 2 in conversation