proc format percent problem

tmm — Thu, 02 May 2013 17:08:43 GMT

I have an ods report that is run on my local machine and does run fine but the log will point out that it could not completely calculate something because the percent is 0/100. I have copied all my tables to our SAS server and the code has not been changed other than remoting in. The prior part of this code runs successfully and outputs my report, however since there are no compute calculations that is no surprise. Here is a snipet of the code that I am running on our server that is causing the problem. I do have the normal stuff above the first define so that is not the issue:

ods tagsets.excelxp file='C:\users\tmill29\documents\data sharing 2013\Data Sharing Market Statistics AL.xls';

proc report nowd split='/' data=cardiac.final12 (where =(mkt_nm='ALABAMA' ));

/*(where =(mkt_nm in ('NYC_NJ_CT','BALT/WASH',/*'GEORGIA','DALLAS','TAMPA','MISSOURI',

/*'WISCONSIN','HOUSTON','ILLINOIS', 'NORTH CAROLINA',/*'ORLANDO','NEW ENGLAND' )AND prov_tin>'000000000'));

/*by mkt_nm;*/

columns prov_tin tin_name prov_tin_cath prov_tin_mbr mkt_cath_rate prov_tin_pci mkt_pci_rate prov_tin_perf mkt_perf_rate

prov_tin_imppac mkt_imppac_rate prov_tin_echo mkt_tte_rate prov_allw_amt facility_allowed;

define prov_tin/group "Provider TIN" missing;

compute prov_tin;

if prov_tin ne ' ' then hold=prov_tin;

if prov_tin eq ' ' then prov_tin=hold;

endcomp;

define tin_name/group "TIN Name" missing ;

compute tin_name;

if tin_name ne ' ' then hold2=tin_name;

if tin_name eq ' ' then tin_name=hold2;

endcomp;

define prov_tin_cath/analysis sum "Cath Count" missing center;

define prov_tin_mbr/analysis sum "Member Count" order=freq missing center;

define mkt_cath_rate/computed "Cath Rate Per/100 Members" format=percent10.2 center;

compute mkt_cath_rate;

mkt_cath_rate=_c3_/_c4_;

endcomp;

define prov_tin_pci/analysis sum "PCI Count" missing center;

define mkt_pci_rate/computed "PCI Rate Per/100 Members" format=percent10.2 center;

compute mkt_pci_rate;

mkt_pci_rate=_c6_/_c4_;

endcomp;

define prov_tin_perf/analysis sum "Perfusion Count" missing center;

define mkt_perf_rate/computed "Perfusion Rate Per 100 Members" format=percent10.2 center;

compute mkt_perf_rate;

mkt_perf_rate=_c8_/_c4_;

endcomp;

define prov_tin_imppac/analysis sum "Implant Count" missing center;

define mkt_imppac_rate/computed "Implant Rate Per/100 Members" format=percent10.2 center;

compute mkt_imppac_rate;

mkt_imppac_rate=_c10_/_c4_;

endcomp;

define prov_tin_echo/analysis sum "Echo Count" missing center;

define mkt_tte_rate/computed "Echo Rate Per/100 Members" format=percent10.2center; *erroring here on SAS server;

compute mkt_tte_rate; ***erroring here on SAS server;

mkt_tte_rate=_c12_/_c4_; ***erroring here on SAS server;

endcomp;

define prov_allw_amt/analysis sum "Provider Allowed/Amount Total" format=dollar15.2 center;

define facility_allowed/analysis sum "Facility Allowed/Amount Total" format=dollar15.2 center;

run;

ods tagsets.excelxp close;

The error in the log is:

Red stick figure man then spaces and another one followed by _ mkt_tte_rate @ _ CARDIAC

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 101262 observations read from the data set CARDIAC.FINAL12.

WHERE mkt_nm='ALABAMA';

NOTE: PROCEDURE REPORT used (Total process time):

real time 19:23.19

cpu time 18.08 seconds

336 ods tagsets.excelxp close;

I know the error has to do with the format=percent10.2 because when I am doing the report based solely on the overall markets, as in all the states, the report processes fine through our server. This is because we have no cases that are 0/100. When I break it down by state, and in this case I am using the state ALABAMA, it is pulling in tax ID numbers which mean it is pointing to 1 providers results. For instance in AL, I have over 100 providers that have 0 in the prov_tin_echo column. So the SAS server is coming back and saying wait, I cannot process 0/100 based on format=percent10.2. Remember, I can run this just fine on my local machine. I just get tons of warnings saying cannot divide 0/100 but the report does finish and no red errors come back. How do I format my percent area to bypass this and just continue. I have tried format=bestDw.p and bestDw. and bestD. I have looked through possible formats and just cannot seem to bypass this error. I have even just removed the format= alltogether.

Re: proc format percent problem

Cynthia_sas — Thu, 02 May 2013 17:58:06 GMT

Hi,

If the same data works on one machine and the exact same data does not work on your server, then this is really an issue for Tech Support. It is going to come down to looking at your data and having someone like Tech Support seeing if they can replicate the issue using the same server setup.

cynthia

Re: proc format percent problem

ballardw — Thu, 02 May 2013 17:59:58 GMT

I would be tempted to change:

format=percent10.2center;

format=percent10.2 center;

It helps to post the actual text of the error message. It looks your error may have been truncated somewhere

Re: proc format percent problem

tmm — Thu, 02 May 2013 18:02:52 GMT

Sorry ballardw, that is just a typo. As for the error, it is posted in my message. We have had all sorts of issues with this new SAS server.

topic Re: proc format percent problem in SAS Procedures

proc format percent problem

Re: proc format percent problem

Re: proc format percent problem

Re: proc format percent problem