Calculating Percent from two columns in proc report not computing

[bananah13]

Hello,

I am trying to calculate positivity yield (column pct) using two columns calculated for my proc report (total and test). The calculate function is not working as desired and returns a column positivity yield column (pct) showing missing values  (both total and test columns are populated with values that do show up in generated report). I am new to proc report, but found this almost exact syntax in SAS documentation, however it is not returning the desired results. Any help is appreciated.

ODS tagsets.excelxp file="C:\Saved\CountyFile.xlm"
style=PEARLJ

options (sheet_name='#BYVAL(county)');

  proc report data=ROOT ;

  title "Missing Data ";
  by county;
  col LocationId Total Test (pct);
  define total/ analysis "Total Records";
  define Test/ analysis "Tests";
  define pct / "Positivity Yield" format=percent8.6;
  define LocationId/ group format=LOCATIONF. "Test Location";
compute pct;
  pct = Test/total;
  endcomp;
run;
ods Tagsets.ExcelXP close;

[PaigeMiller]

Please provide a portion of your data as SAS data step code (and not in any other format). You can type the SAS data step code in yourself, or have it created automatically in which case here are instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Also if there are errors in the log, show us the entire log for this PROC REPORT (from the start of PROC REPORT to the last NOTE after PROC REPORT, with nothing chopped out). Paste the log into the window that appears when you click on the </> icon. 

For your future benefit, saying 

however it is not returning the desired results

and providing no other information never helps. We don't know what your data looks like. We don't know what happened that is not the desired results. So we always request for more information. So from now on, don't make us ask, provide the details in the LOG and/or the incorrect output; and a portion of your data.

[bananah13]

Apologies! The desired result is a percentage calculated from dividing columns (test / total) *100. Here is what is being returned. 

Proc report being generated:

Test Location	Total	Tests	Positivity Yield
One	23	3	.
Two	15	4	.
Three	119	22	.

Data input example:

location	test	total	county
1	1	1	1
2	1	1	2
3	0	1	12
3	0	1	2
2	0	1	7
1	1	1	4
2	0	1	1

I am getting a note in the bug log saying variables are uninitialized

NOTE: Variable total is uninitialized.
NOTE: Variable Test is uninitialized.

[ballardw · Accepted solution]

---

@bananah13 wrote:

Apologies! The desired result is a percentage calculated from dividing columns (test / total) *100. Here is what is being returned. 

I am getting a note in the bug log saying variables are uninitialized

NOTE: Variable total is uninitialized.
NOTE: Variable Test is uninitialized.

---

Not a bug but standard behavior for Proc Report. Consider this code, which uses a data set you can test:

proc report data=sashelp.class;
  column name height weight (ratio);
  define name /group;
  define height/analysis;
  define weight/analysis;
  define ratio/format=percent8.3;
  compute ratio;
  /* this "computation" creates Variable uninitialized notes*/
/*     ratio=height/weight;  */
     ratio= _c2_/_c3_;
  endcomp;
run;

Because the "variable" could appear in different places (consider Across) the name of a variable alone does not reference the correct value. You can reference the result column, _c2_ is column 2 or the position that Height appears in above.

Depending on specific calculations there are other options which why SAS published an entire book just on Proc Report.

[bananah13]

Yay! It worked. I guess I just needed to point to the location of the columns that I wanted computed, which shows my ignorance around Proc Report. Thank you. Final code that worked (sorry its not in data step format; can't link from my place of work):

ODS tagsets.excelxp file="C:\Saved\CountyFile.xlm"
style=PEARLJ

options (sheet_name='#BYVAL(county)');

  proc report data=ROOT ;

  title "Missing Data ";
  by county;
  col  LocationId  Total  Test  pct;
  define total/ analysis "Total Records";
  define Test/ analysis "Tests";
  define pct / "Positivity Yield" format=percent8.6;
  define LocationId/ group format=LOCATIONF. "Test Location";
compute pct;
  pct = Test/total;

  pct=_c2_/_c3_;
  endcomp;
run;

ods Tagsets.ExcelXP close;