PROC Report outputting one observation only

[vegan_renegade]

I have an issue with proc report outputting only one observation and with the "Test Approved Date" (format MMDDYY10.) column with asterisks (or hashes when I export to xlsx using ods excel). The kicker is that the actual table in SAS shows all the data correctly, with just over 300 obs. but outputting it in the results window via proc report shows just the one obs. 

When outputting to xlsx, clicking the hashes shows a number (not date) in the formula bar, but adjusting the column width does not resolve the hashes. I'm stumped on this one.

/*Sum test results and group by approved date*/
proc sql;
create table positivity_rates as
select Appr_Date, COUNT(Negative) as Negative, COUNT(Positive) as Positive, COUNT(Other) as Other
from outreach11
group by Appr_Date;
quit;

/*Adds a Total and Positivity rate columns*/
data positivity_rates1;
set positivity_rates;
Total=SUM(Positive, Negative, Other);
Positivity_Rate=Positive/Total*100;
format Positivity_Rate PCTFMT.;
run;

proc report data=positivity_rates1 
style(header) = {background=#32312F foreground=white font_weight=bold};
columns Appr_Date Negative Positive Other Total Positivity_Rate; 
define Appr_Date       / "Test Approved Date";
define Positivity_Rate / "Positivity Rate";
run;

Proc report output in results window:

Output by proc report

Excel output:

[ballardw]

The issue with the *********** output is about 99.9% cause by using a Date format like MMDDYY with a value that is actually a datetime. SAS uses days to count dates and seconds to count time and datetime.

Try a DTDATE9 format and see if you get reasonable dates. If so, then your value is datetime.

When the formatted value is the same then all the values are in one group. This is design feature. However when you exceed the range of the values for date type format (year 9999 currently) then you get the asterisks and all the formatted values are in one group.

Solutions in no particular order.

1) Use the datepart function on your datetime value to extract just the date portion of the value and use the MMDDYY format with that variable. (more durable when reusing the data set)

2) create a custom format for datatype=datetime that will display mmddyy ( a good learning experience for the interested reader)

3) use the DTDATE format. (quickest)

4) there's probably a COMPUTE block solution in Proc report with the date variable as well using the datepart function.

You also see the totals of all the counts because Proc Report by default sums numeric variables unless you force a list output.

[vegan_renegade]

Ok I converted the format to DTDATE9. using format. Now it looks like this- note still only one obs outputting, not the >300 I have:

I will note that I have other code using the mmddyyyy. format being output correctly using the same variable (appr_date), with the same set table, with very similar code. For example, this code will output the data correctly in proc report:

/*Creates counts by date*/
proc sql;
create table counts_date as
select Appr_Date, Draw_Location, COUNT(Negative) as Negative, COUNT(Positive) as Positive, COUNT(Other) as Other
from outreach11
group by Appr_Date, Draw_Location;
quit;

/*Adds a Total and Positivity rate column*/
data counts_date1;
set counts_date;
Total=SUM(Positive, Negative, Other);
Positivity_Rate=Positive/Total*100;
format Positivity_Rate PCTFMT.;
run;

proc report data=counts_date1
style(header) = {background=#32312F foreground=white font_weight=bold};
columns Appr_Date Draw_Location Negative Positive Other Total Positivity_Rate;
define Appr_Date       / "Test Approved Date";
define Draw_Location   / "Testing Location";
define Positivity_Rate / "Positivity Rate";
run;

[ballardw · Accepted solution]

Try this:

proc report data=positivity_rates1 
style(header) = {background=#32312F foreground=white font_weight=bold};
columns Appr_Date Negative Positive Other Total Positivity_Rate; 
define Appr_Date       / GROUP "Test Approved Date";
define Positivity_Rate / "Positivity Rate";
run;

You did not tell Proc Report that Appr_date is a grouping variable, so by default it summed all the dates.

I suspect that when you did the report with that location variable that location was character and so defaults to "group" role and brought the date along with it.

Here is small example of what I think happened with this report. The first proc report is a simple version of yours. Note the "date" shown. The second forces grouping on the date.

data example;
   input somedate :date9. value;
   format somedate mmddyy10.;
datalines;
01Jan2021   423
02Jan2021   123
03Jan2021   456
04Jan2021   100
05Jan2021   222
;

proc report data=example;
   title "Without group";
   columns somedate   value;
   define somedate / "some label";
   define value    / "the numbers";
run;

proc report data=example;
   title "With group";
   columns somedate   value;
   define somedate / "some label" group;
   define value    / "the numbers";
run;title;

Moral of the story; DEFINE the roles of variables: group across analysis computed display or order.

[vegan_renegade]

@ballardw Thank you. Adding GROUP did the trick. I thought proc report would export the data exactly as the dataset shows, but I learned that that's not the case.