BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fbarry
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;

View solution in original post

5 REPLIES 5
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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
SAS Super FREQ


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
Calcite | Level 5

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
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3543 views
  • 0 likes
  • 2 in conversation