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;
... View more
