great, the proc print sum is working perfectly thank you. Didn't realize I could use that! 🙂 ... View more

Here is my total program: I'll need to sum the total of the rate column for each site within a contractor and they should each get their own tab in the excel. I'll look into the sum option in proc print. %let conlist=51;*73 75 85 88 94 95 97; options validvarname=any mprint mlogic symbolgen; %macro conloop(); %do i=1 %to 1;*8; %let contract=%scan(&conlist,&i); *merge PEID to federal/HRI output; proc sort data=work.fedNew_MBR_&contract; by ServiceSite name; run; proc sql; create table fedmerge&contract as select a.participantID, a.clientname, a.DOB, a.servicesite, a.dos, a.procedure, a.MBRlabel, a.paymentsource, a.rate,input(a.rate,comma32.) as newrate, sum(calculated newrate) as sumnewrate, b.servicesite, b.name, b.addressline1, b.addressline2, b.addressline3, b.city, b.statecd, b.zip from work.fedNew_MBR_&contract as a left join work.credfilespeid as b on input(a.servicesite,best8.)=input(b.servicesite,best8.) group by a.servicesite; quit; *federal output; ods listing close; ods tagsets.excelxp file="J:\Data Requests\Internal Data Requests\Fiscal\July 2018 MBR\Federal\New_MBR_&contract._Federalmerg.xls" style=printer options(orientation='landscape' embedded_titles='yes' embedded_footnotes='yes' ABSOLUTE_COLUMN_WIDTH='15' SUPPRESS_BYLINES='yes' SHEET_LABEL='Sitecode=#BYVAL(ServiceSite) '); title1 "New York State Department of Health Cancer Services Program"; title2 "Cancer Services Program"; title3 "Patient Services Summary"; title4 justify=LEFT '#BYVAL(name)'; footnote1 "Report Prepared: %sysfunc(today(),mmddyy10.)"; proc print data=work.fedmerge&contract uniform noobs style=[font_size=12]; by ServiceSite name; var ParticipantID ServiceSite DOS Procedure MBRLabel PaymentSource Rate newrate sumnewrate name addressline1 addressline2 addressline3 city statecd zip/style(header)={backgroundcolor=white}; pageby ServiceSite; *where PaymentSource="Federal"; run; ods tagsets.excelXP close; ods listing; *merge PEID to federal crc output; proc sort data=work.fedcrcNew_MBR_&contract; by ServiceSite name; run; proc sql; create table fedcrcmerge&contract as select a.participantID, a.clientname, a.DOB, a.servicesite, a.dos, a.procedure, a.MBRlabel, a.paymentsource, a.rate,input(a.rate,comma32.) as newrate, sum(calculated newrate) as sumnewrate,b.servicesite, b.name, b.addressline1, b.addressline2, b.addressline3, b.city, b.statecd, b.zip from work.fedcrcNew_MBR_&contract as a left join work.credfilespeid as b on input(a.servicesite,best8.)=b.servicesite group by a.servicesite; quit; *output for federal CRC; ods listing close; ods tagsets.excelxp file="J:\Data Requests\Internal Data Requests\Fiscal\July 2018 MBR\Federal CRC\New_MBR_&contract._FederalCRCmerg.xls" style=printer options(orientation='landscape' embedded_titles='yes' embedded_footnotes='yes' ABSOLUTE_COLUMN_WIDTH='15' SUPPRESS_BYLINES='yes' SHEET_LABEL='Sitecode=#BYVAL(ServiceSite) '); title1 "New York State Department of Health Cancer Services Program"; title2 "Cancer Services Program"; title3 "Patient Services Summary"; title4 justify=LEFT '#BYVAL(name)'; footnote1 "Report Prepared: %sysfunc(today(),mmddyy10.)"; proc print data=work.fedcrcmerge&contract uniform noobs style=[font_size=12]; by ServiceSite name; var ParticipantID ServiceSite DOS Procedure MBRLabel PaymentSource Rate newrate sumnewrate name addressline1 addressline2 addressline3 city statecd zip/style(header)={backgroundcolor=white}; pageby ServiceSite; *where PaymentSource="Federal CRC"; run; ods tagsets.excelXP close; ods listing; *merge state SFS to state MBR output; proc sort data=work.stateNew_MBR_&contract; by ServiceSite sfs_provider_name; run; proc sql; create table statemerge&contract as select a.participantID, a.servicesite, a.dos, a.procedure, a.MBRlabel, a.paymentsource, a.rate,input(a.rate,comma32.) as newrate, sum(calculated newrate) as sumnewrate, b.servicesite,b.sfsvendorid, b.sfs_provider_name, b.hri_and_or_SFS_address, b.sfs_address from work.stateNew_MBR_&contract as a left join work.credfilesfs as b on input(a.servicesite,best8.)=b.servicesite group by a.servicesite; quit; *output for state; ods listing close; ods tagsets.excelxp file="J:\Data Requests\Internal Data Requests\Fiscal\July 2018 MBR\State\New_MBR_&contract._Statemerg.xls" style=printer options(orientation='landscape' embedded_titles='yes' embedded_footnotes='yes' ABSOLUTE_COLUMN_WIDTH='15' SUPPRESS_BYLINES='yes' SHEET_LABEL='Sitecode=#BYVAL(ServiceSite) ' ); title1 "New York State Department of Health Cancer Services Program"; title2 "Cancer Services Program"; title3 "Patient Services Summary"; title4 justify=LEFT '#BYVAL(sfs_provider_name)'; footnote1 "Report Prepared: %sysfunc(today(),mmddyy10.)"; proc print data=work.statemerge&contract uniform noobs style=[font_size=12]; by ServiceSite sfs_provider_name; var ParticipantID ServiceSite DOS Procedure MBRLabel PaymentSource Rate newrate sumnewrate sfsvendorid sfs_provider_name hri_and_or_SFS_address sfs_address/style(header)={backgroundcolor=white}; pageby ServiceSite; *where PaymentSource="State"; run; ods tagsets.excelXP close; ods listing; *Add state supplement output for months of April, May, June; %end; %MEND; %conloop; ... View more

Good Point. I am using proc print and ods tagsets excel to export the data. I need it to end up in excel spreadsheets. I can look into proc report and proc tabulate, I am somewhat familiar. Thanks!! ... View more

advice on how to sum and insert the totals into the bottom of the table?   proc sql; create table mergembr as select a.participantID, a.clientname, a.DOB, a.servicesite, a.dos, a.procedure, a.MBRlabel, a.paymentsource,a.rate,input(a.rate,comma32.) as newrate, b.servicesite, b.sfs_provider_name, b.hri_and_or_SFS_address, b.sfs_address from work.stateNew_MBR_51 as a left join work.credfilesfs as b on input(a.servicesite,best8.)=b.servicesite; insert into mergembr select sum(calculated newrate) from work.stateNew_MBR_51; quit; ... View more

ahhh yes...It is converting like I need it to! Now I am going to try to sum it and insert the sum as a total in the output ... View more

I am having some luck with this but it is removing the parenthesis when I think I'll ultimately need them to be negatives so that it sums up correctly ...   proc sql; create table mergembrnew as select servicesite, dos, rate, input(compress(rate,'0123456789','k'),dollar32.2) as newrate from work.stateNew_MBR_51; quit; run;   ... View more