change variable from char (with $ signs and paranthesis) to num, then sum and add total to SQL

[annie_doh]

Hello,

I have a character variable named "Rate" that I am importing from excel that is $38. It represents a monetary value and appears has either $XXX.XX or ($XXX.XX) --The parenthesis represent negative amounts. I am trying to work with this variable in proc sql to convert it from character to numeric, then sum it up and add total to the table so I can then export it again in excel using ods tagsets and proc print. This is all occurring in a macro as well.

Here is a snippet of my code:

proc sql;

create table mergembr as

select a.participantID, a.servicesite, a.dos, a.procedure, a.MBRlabel,

a.paymentsource, input(a.rate,38) format comma12.2, 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(a.rate)

from work.stateNew_MBR_51;

quit;

[ballardw]

Wrong informat:

data example;
   string= '($1,456,899,234.56)';
   num = input(string,comma32.);

run;

Note that the limit on the comma format is 32. If there are actually 38 characters in a currency value you will need to do something. Without examples it is hard to tell what that something might actually be.

[annie_doh]

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;

[annie_doh]

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

[annie_doh]

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;

[SuryaKiran]

Try nlmny10. or rstdodn10. format if you want to see parenthesis for negative currency values.

If you want totals at the bottom of the table then PROC SQL might not be the right approach. You may need to try PROC REPORT or PROC TABULATE. Show provide some sample data in the form of a datastep please.  

[annie_doh]

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!!

[Cynthia_sas · Accepted solution]

Hi:
I'm not sure why you need SQL to do the final summary, PRINT, REPORT and TABULATE will all summarize negative and positive numbers correctly. PROC PRINT has the SUM statement; PROC REPORT has the BREAK and RBREAK statements and PROC TABULATE has the ALL keyword.

Cynthia

[annie_doh]

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;

[annie_doh]

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

[SuryaKiran]

If your using SAS 9.4 M3 or later, then try ODS EXCEL instead of ODS EXCELXP.TAGSETS  to create native Excel files (xlsx). Only thing you need to worry about ODS EXCEL is insufficient memory issue when your trying to send large table into Excel. ODS Tagsets inserts one row at a time, where as ODS EXCEL will insert the whole table at once which cause insufficient memory issues. 

Consider this SAS Paper if you prefer ODS Excel