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;
@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.
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.
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.
@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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.