Creating very large report in PROC REPORT

[RandoDando]

I am creating a very large report using PROC REPORT, which would result in about 15,000 rows in the output.  Because this is more than the results window can handle, I have suppressed them and am exporting the results to an Excel file.  The issue is with the report itself.  

First, I am using a data set which has data for the variable "SCORE" for 5 time periods.  The remainder of the data are the same for those 5 periods.  The data set is structured so that the data for all columns EXCEPT "SCORE" and "DATE" repeat 5 times, once for each date.  It is sorted by DATE.  

I am wanting PROC REPORT to group the data by DATE and show the scores for each of those dates going across.  However, it isn't grouping the way I want.  See attached image of the kind of output I am getting.   I want the scores shown in the image to appear in the same row.  I am using the comma between the across and grouping variables in the columns statement, so I'm not sure what the issue is.  

Also, in my original data set some observations are blank for some of the variables I am grouping on in the report.  I believe this is what is causing the procedure to drop cases.  How can I retain all of the observations of my original data set, even if some of the values for the grouping variables are blank? 

ods excel 
file= "\\.....xlsx" options(sheet_name="sheet1");
ods results off;
proc report data=ALL nowd ;
columns ("Information" Office  id name SOA procurement_type Crit_score ) ("Availability"  state_cnt)
("Previous" PREV_DT  PREV_SCORE 
HS ) ("Current RAP" STATUS PARTICIPANT)
("County" state_cd county_name)  ("Score (Previous 6 Weeks)" DATE, score);

define Office /"Office" group;
define id /"ID" group;
define NAME /"Name" group;
define SOA /"SOA" group center;
define procurement_type /"Procurement Type"  group format=$Procure.;
define CRIT_SCORE  /"Criticality Score" group;
define DATE/across ""  order=data center;
define score   /analysis ""  format=missingz. style(column)={background=colorflag.} center;
define county_name /"County Name" group;
define state_cd /"State" group;
define state_cnt /"Number of Active in State" group;
define PREV_DT  /"Previous Date" group;
define PREV_SCORE /"Previous Score" group;
define HS/ 'Percent HS' group right format=percent10.0;
define RAP_STATUS/ "Status" group;
define PARTICIPANT/ "Participant" group;

compute CRIT_score;
if CRIT_score = 5 then do;
     call define(_col_, "style", "style=[backgroundcolor=VIYPK font_weight=bold]");
end;
if CRIT_score = 4 then do;
                call define(_col_, "style", "style=[backgroundcolor=VIO font_weight=bold]");
                end;
if CRIT_score = 3 then do;
                call define(_col_, "style", "style=[backgroundcolor=BIOY font_weight=bold]");
                end;
if CRIT_score = 2 then do;
                call define(_col_, "style", "style=[backgroundcolor=PAOY font_weight=bold]");
                end;
if CRIT_score = 1 then do;
                call define(_col_, "style", "style=[backgroundcolor=BILG font_weight=bold]");
                end;
endcomp;

COMPUTE OFFICE;
      IF OFFICE NE "" THEN DUMMY=OFFICE;
      ELSE OFFICE=DUMMY;
                  endcomp;


run;quit;

ods excel close;
ods results on;

[PaigeMiller]

Can you show us the column headers on the Excel file that you are showing? It's not really clear what I am looking at.

[RandoDando]

Unfortunately, I had to kill the process because it was taking too long and the Excel file got corrupted. From what I was able to see a minute ago, I was able to fix the issue with the missing observations by adding the "missing" option to the procedure. However, the grouping is still an issue. As well as the processing time, but that may be something I have to live with given the size of the data set.

Here is something I drew up to show what the resulting output in Excel currently looks like. I want all the scores going across (DATES of week 1 to 5) for each NAME to appear in the first row of data.

[PaigeMiller]

The code in your first message did not produce the spreadsheet in your second message. Can we please see the code that produced the spreadsheet in your second message?

[RandoDando]

There isn't any code for the spreadsheet in my second message. I just drew that up on the fly to give you an idea of what my output file looked like. The REPORT procedure is still running so I do not have any output to show.

[PaigeMiller]

I'm not good at providing solutions when there is a mismatch between the code provided and the output provided. 😕

[ballardw]

Here's an idea:

Run report with fewer records so you can actually provide an example.

proc report data=ALL (obs=50) nowd ;

will run the report using only 50 records. Which should complete relatively quickly but show some results. Depending on the order of your data you might have to increase the OBS= value to demonstrate the actual problem.

Better would be to provide some example data in the form of a data step so we can actually test your code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[RandoDando]

I had a missing "/Group" value on one of the variables in the define statement.  That appears to have fixed the grouping issue.  I will have to examine the output more closely to check whether any observations were dropped.  

I made the fix when I noticed it in the the SAS code I pasted on this post, but didn't make the fix in the code on my end.  🙃