ODS MSOFFICE2K - PROC REPORT only printing values from first record for one variable

[Phil0917]

Hi Everyone,

  I am having a strange issue occurring with an ODS MSOFFICE2K output.  I am creating an Excel file from a PROC REPORT, and I am including a variable that needs to be displayed, along with a complimentary variable that will be suppressed from printing, but is needed in a compute block in order to conditionally highlight values.  This seems to work fine for every column in the resulting file except for one.  In my code (attached below), for the variable AERATE, only the value in the first record appears in the output and all other rows appear blank (even though the dataset contains values for subsequent rows.  If I comment out all references to AERATE_FLG, the variable displays correctly, but when AERATE_FLG is included, it only prints the value for the first row.  Any ideas???

Thanks!

-Phil

Code:

title;

footnote;

ods listing close;

ods msoffice2k file="\\na1sasfile1\BSU\BSUBS326-BSC326\Listings\Report\Compliance Reports\Compliance Report &sysdate9..xls";

proc report data=all split='*' nowd style(report)={foreground=black background=white};

   columns grp

           aa,(site_no pi_full_name site_status actdt enrolled icdt)

           pssv_flg siv_ready_flg ic_smc_flg imv_trip_flg bb,(pssv_dur siv_ready ic_smc imv_trip)

    days_fpi_flg pats_mo_flg enr_act_flg cc,(days_fpi pats_mo enr_act)

           basedays_flg2 comppct_flg de_pct_flg qrate_flg oquery_flg osquery_flg sig_flg dd,(basedays_flg comppct de_pct qrate percent osqnum sig)

           ee,(enrpp_rate pdic_rate pdfund_rate pdwind_rate pdoth_rate sitelev)

           aerate_flg ff,(aerate)

           gg,(noncomp turnover_key turnover_rep ques non_resp request);

   define grp / order noprint;

   define aa / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey];

   define bb / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=grey];

   define cc / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=gold];

   define dd / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue];

   define ee / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink];

   define ff / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=green];

   define gg / across " " style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange];

   define site_no / display "Site Number" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                          style(column)={just=c vjust=c font_size=12pt background=white cellwidth=70 htmlstyle="mso-number-format:'0000'"};

   define pi_full_name  / display "Principal Investigator" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                                           style(column)={just=c vjust=c font_size=12pt background=white cellwidth=200};

   define site_status   / display "Activation Status" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                                      style(column)={just=c vjust=c font_size=12pt background=white cellwidth=150};

   define actdt         / display "Date Activated" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                                   style(column)={just=c vjust=c font_size=12pt background=white cellwidth=120};

   define enrolled      / display "# Patients Enrolled" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                                        style(column)={just=c vjust=c font_size=12pt background=white cellwidth=85};

   define icdt          / display "FPI" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightgrey]

                                        style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define pssv_flg      / display noprint;

   define pssv_dur      / display "SSQ Received to PSSV (Days)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=grey]

                                                                style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define siv_ready_flg / display noprint;

   define siv_ready     / display "Site Readiness to SIV (Days)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=grey]

                                                                 style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define ic_smc_flg    / display noprint;

   define ic_smc        / display "FPI to First SMC (Days)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=grey]

                                                            style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define imv_trip_flg  / display noprint;

   define imv_trip      / display "IMV to Trip Report Finalized (Days)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=grey]

                                                                        style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define days_fpi_flg  / display noprint;

   define days_fpi      / display "Activation to Enrollment (Days)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=gold]

                                                                    style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define pats_mo_flg   / display noprint;

   define pats_mo       / display "Enrollment (Patients/Site/Month)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=gold]

                                                                     style(column)={just=c vjust=c font_size=12pt background=white cellwidth=168} format=8.2;

   define enr_act_flg   / display noprint;

   define enr_act       / display "Enrollment Before Activation (Y/N)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=gold]

                                                                       style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define basedays_flg2 / display noprint;

   define basedays_flg  / display "Baseline CRF Completion >14 Days (Number of Patients)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                                                          style(column)={just=c vjust=c font_size=12pt background=white cellwidth=115};

   define comppct_flg   / display noprint;

   define comppct       / display "CRF Completion %" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                     style(column)={just=c vjust=c font_size=12pt background=white cellwidth=97} format=8.1;

   define de_pct_flg    / display noprint;

   define de_pct        / display "Data Entry Before Signed ICF (% Patients)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                                              style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.1;

   define qrate_flg     / display noprint;

   define qrate         / display "Query Rate (% per Patient)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                               style(column)={just=c vjust=c font_size=12pt background=white cellwidth=93} format=8.1;

   define oquery_flg    / display noprint;

   define percent       / display "Queries Older Than 21 Days (%)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                                   style(column)={just=c vjust=c font_size=12pt background=white cellwidth=95} format=8.1;

   define osquery_flg   / display noprint;

   define osqnum        / display "Safety Queries Older Than 21 Days" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                                      style(column)={just=c vjust=c font_size=12pt background=white cellwidth=90};

   define sig_flg       / display noprint;

   define sig           / display "PI Signature Completion (%)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightblue]

                                                                style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100};

   define enrpp_rate    / display "Event Not Reported Per Protocol" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                                                    style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.2;

   define pdic_rate     / display "Informed Consent" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                                     style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.2;

   define pdfund_rate   / display "Visit / Follow Up Not Done" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                                               style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.2;

   define pdwind_rate   / display "Visit Out of Window" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                                        style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.2;

   define pdoth_rate    / display "Other" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                          style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.2;

   define sitelev       / display "Site Level (# per Site)" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=lightpink]

                                                            style(column)={just=c vjust=c font_size=12pt background=white cellwidth=95} format=8.2;

   define aerate_flg    / display noprint;

   define aerate        / display "SAE Rate" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=green]

                                             style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} format=8.1;

   define noncomp       / display "Non-Compliance Issues" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                          style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   define turnover_key / display "Turnover of Key Site Staff" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                              style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   define turnover_rep / display "Repeated Site Staff Turnover" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                                style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   define ques         / display "Types of Site Questions" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                           style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   define non_resp     / display "Non-Responsive / Significant Compliance Concerns" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                                                    style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   define request      / display "Client Requested or Audit Request / Result" style(header)=[just=c vjust=c font_face=Arial font_size=12pt font_weight=bold background=orange]

                                                                              style(column)={just=c vjust=c font_size=12pt background=white cellwidth=100} ;

   compute pssv_dur;

      if pssv_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute siv_ready;

      if siv_ready_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute ic_smc;

      if ic_smc_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute imv_trip;

      if imv_trip_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute days_fpi;

      if days_fpi_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute pats_mo;

      if pats_mo_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute enr_act;

      if enr_act_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute basedays_flg;

      if basedays_flg2=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute comppct;

      if comppct_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute de_pct;

      if de_pct_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute qrate;

      if qrate_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute percent;

      if oquery_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute osqnum;

      if osquery_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute sig;

      if sig_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

   compute aerate;

      if aerate_flg=1 then do;

      call define(_col_,"style","style=[background=red]");

   end;

   endcomp;

run;

ods msoffice2k close;

ods listing;

[ballardw]

A few records of data to experiment with that show the behavior would be helpful. Especially if presented as a datastep.

[Phil0917]

There are over 60 variables in the dataset, so it would be exhaustive to try and create these as examples in a data step.  However, I may have already come across a solution (or at least identified a limitation).  It seems that the issue was caused by having only 1 variable under the "across" variable (in this case, the variable "FF", with only "AERATE" under it).  For regular display, this is not an issue however, it seems that this is an issue if you are trying to include these variables within a compute block for traffic lighting purposes.

To remedy this, I created a dummy variable containing just blanks, and included it in the definition under the across (so now the AERATE and this dummy variable are both under "FF").  Then, I just define this dummy variable as a "display noprint", so it does not generate a column in the output.  Once I do that, everything seemed to work fine.

Not sure what the underlying mechanism is that causes this to be the case, but apparently trying to format through a compute block on a variable that is the only variable under an across variable causes the data values to disappear in the output.  Once you include another variable under that across variable, everything works as expected.