PROC Report vs. Proc Print - Inserting a blank line after a group

[fbarry]

I posted the following question in another forum and the response I received suggested I use PROC Report instead of PROC Print and post my question here. My question is:

I have the following list of id numbers in a SAS dataset:

1000

1000

2000

3000

3000

3000

I want to write out the dataset and put a blank line in between each group of ids i.e.

1000

1000

2000

3000

3000

3000

Searching online I keep find the "blankline" option but this inserts a blank line every n rows - which doesn't work for me as the number of rows for each id varies. I have tried the "by" option but it creates a new worksheet for each id and I need to have all the ids in the same worksheet.

My SAS code to write out the data to an EXCEL file is below - can I achieve the same result with PROC Report?

ods _all_ close;

ods tagsets.ExcelXP file='n:\nih succeed\reporting\B-weeklyreporting\SucceedDueDatesBeatrice2.xml' style=Printer;

ods tagsets.ExcelXP options(sheet_name='Summary Beatrice' absolute_column_width='15,15,45,15');

proc print data=allwrkout noobs label;

    where (screen_site=1);

    var screen_site / style(column)=[just=center] style(header)=[just=center];

    var survey / style(column)=[background=svcolcd. just=center] style(header)=[just=center];

    var status / style(column)=[foreground=stcolcd. just=center] style(header)=[just=center];

    var study_id due_date baseline_date / style(column)=[just=center] style(header)=[just=center];

    label screen_site = 'Site'

          survey = 'Survey'

          status = 'Status'

          study_id = 'Study Id'

          due_date = 'Due Date'

          baseline_date = 'Baseline Survey Start Date';

    format screen_site site. status stcd. survey svcd.;

run; quit;

Thank you!

[Cynthia_sas · Accepted solution]

Hi:

  As you've discovered, the BLANKLINE option is all you get with PROC PRINT. You have to move to the COMPUTE block in PROC REPORT to do what you want. Run the code below and note that there is a blank line after every "group" for age.

Cynthia

ods tagsets.excelxp file='c:\temp\blankline.xml' style=htmlblue;

proc report data=sashelp.class;
  where age in (11, 12, 13);
  column age name sex height weight;
  define age / order
         style(column)=Header;
  define name / order style(column)={background=lightpink};
  define sex / display style(column)={background=lightyellow};
  define height / display;
  define weight / display;
  compute after age;
    line ' ';
  endcomp;
run;
     
ods _all_ close;

[fbarry]

Thank you!

And on a similar topic - I've been asked to create a report such that every other id group is shaded - so for example:

2001 - blue

2001 - blue

2002 - not shaded

2003 - blue

2003 - blue

2003 - blue

2004 - not shaded

2004 - not shaded

I see with Proc Report I can shade a whole row but cannot figure out how to achieve the above.

[Cynthia_sas]

Hi, again, it's a COMPUTE block with an IF statement. Something like:

compute grpvar;

  if grpvar in ( 2001, 2003) then call define(_row_,'style','style={background=lightblue}');

  else if grpvar in ( 2002,2004 ) then call define(_row_,'style','style=Data');

  else if grpvar = 2005 then call define(_row_,'style','style={background=lightpink}');

endcomp;

(you might have to change the logic based on your data, etc)

  There are some examples of COMPUTE blocks in this paper. http://support.sas.com/resources/papers/proceedings13/366-2013.pdf and there's also a reference section with more papers to read on the topic.

cynthia

[fbarry]

Thanks again.

I created a dummy 1/0 variable that is attached to each id (its called lcol) and am using the following code:

compute lcol;

    if lcol=0 then call define (_row_, "style","style=[background=red]");

endcomp;

I think this is what you recommended but I keep getting the error message "Variable lcol is uninitialized". I have lcol listed in the column statement and I have a define statement for it also and I've double checked that the variable is in the dataset. Also, in the final report, although I want to use lcol to determine the background color, I do not need this variable in the final report.

[Cynthia_sas]

Hi, is LCOL in your COLUMN statement. Did you read the posted paper link? Proc REPORT has some special ways of working. One way is that you can ONLY use variables like dummy variables in PROC REPORT if they are on the COLUMN statement or created in a COMPUTE block.

  Further if you have this:

  column grpvar lcol var1 var2;

then you could NOT use LCOL in the computer block for GRPVAR. AND, if you just have this: column grpvar var1 var2; (but LCOL is in the data, but not in the COLUMN statement, then you will NOT be able to use LCOL in a COMPUTE block.

cynthia