Adding an empty row after each by group

[mlogan]

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,

[PGStats · Accepted solution]

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;

[mlogan]

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;

[Tom]

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.

[mlogan]

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.

[Tom]

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.

[mlogan]

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.

[PeterClemmensen]

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;