topic Re: Adding an empty row after each by group in SAS Data Management

Adding an empty row after each by group

mlogan — Sat, 25 May 2019 02:57:35 GMT

Hi there,

Can someone please tell me how to insert a blank line after each by group. Sorry I could not come up with any code but if you can write the code with sashelp.class table that would be great.

If it is not possible to do it on sas table please show me how to write that option during exporting to excel.

Thanks,

Re: Adding an empty row after each by group

PGStats — Sat, 25 May 2019 05:32:45 GMT

This might do:

proc sort data=sashelp.class out=class; by sex age; run;

data want;
set class;
by sex age;
output;
if last.age then do;
    call missing(of _all_);
    output;
    end;
run;

Re: Adding an empty row after each by group

PeterClemmensen — Sat, 25 May 2019 07:08:51 GMT

proc sort data=sashelp.iris out=iris; 
   by species; 
run;

data want;
   do until (last.species);
      set iris;
      by species;
      output;
   end;
   call missing(of _all_);
   output;
run;

Re: Adding an empty row after each by group

mlogan — Sat, 25 May 2019 16:12:07 GMT

Thanks PGStats. It worked perfect. Would you be able to tell me what the code would be for flagging each by group with chronological number. I added some lines to your code, but I also count the black space.

proc sort data=sashelp.class out=class; by sex age; run;

data want;
set class;
by sex age;
output;
if last.age then do;
call missing(of _all_);
output;
IF first.ID THEN count=1;
ELSE count+1;
end;

Re: Adding an empty row after each by group

Tom — Sat, 25 May 2019 16:57:04 GMT

Look more carefully at the code you have written and follow the logic of it. Proper indentation will help.

data want;
  set class;
  by sex age;
  output;
  if last.age then do;
    call missing(of _all_);
    output;
    IF first.ID THEN count=1;
    ELSE count+1;
  end;
run;

Let's just assume that you meant first.sex instead of first.id

Notice how the test for first.sex is inside block that only runs at last.age. So that can only be true when first age group in that sex group has only one observation.

Also notice how the variable COUNT is incremented after the record has already been output.

It is not clear what you want to count, but the CALL MISSING() is going to blank out the COUNT variable with all of the rest.

But my real question is why the heck would you want to add blank observations? Are you just trying to produce a report? PROC PRINT and PROC REPORT can produce reports that insert blank lines after the groups.

Re: Adding an empty row after each by group

mlogan — Sat, 25 May 2019 17:21:46 GMT

Hi Tom, I want to insert a blank space so that I can export it to excel from sas table without an extra step of PROC REPORT.

Re: Adding an empty row after each by group

Tom — Sat, 25 May 2019 17:50:43 GMT

How is writing it to Excel with PROC REPORT or PROC PRINT an extra step? Your step to insert the blank observations is already an extra step.

Re: Adding an empty row after each by group

mlogan — Sun, 26 May 2019 03:55:15 GMT

Hi Tom, Would you please write the code with PROC REPORT that will export the data to Excel while inserting a blank space after each by group. Thank for your help.