CODEWITHSUNDEEP
sas
Graham Smith
Graham Smith

Reputation: 3

How to insert a row in SAS in relation to another row

I've got a table with several different groups where I'm calculating the totals after each group, and I'd like to insert a blank row before and after each total row.

Is there a way to set up an if then statement in relation with something like:

if name='TOTAL' then insert blah blah blah before and after

I'm pretty new to SAS but all the other questions I saw related to inserting rows at fixed points (usually beginning/end) and not in relation to specific other rows

Upvotes: 0

Views: 1158

Answers (1)

Dominic Comtois
Dominic Comtois

Reputation: 10401

As mentioned in the comments, you might be better off using proc report to do that sort of things. But just for the sake of it, here's a solution anyway!

Here we use a descending sorting method, but an alternative approach for inserting blank rows before totals would be to use temporary variables to store every value on the row, then doing a call missing on everything, then an output, and then get back values from the temp variables and do another output.)

data have;
  informat id 3. rowType $12. number 3.;
  input id rowType number;
  datalines;
1 value 111
1 value 222
1 total 333
2 value 000
2 value 999
3 total 999
;

data tmp;
  set have;
  rownum + 1;
  if rowType = "total" then do;
    output;
    call missing(id, rowType, number);
    rownum + 1;
    output;
  end;
  else output;
run;

proc sort data=tmp out=tmp(drop=rownum);
  by descending rownum;
run;

data tmp2;
  set tmp;
  rownum + 1;
  if rowType = "total" then do;
    output;
    call missing(id, rowType, number);
    rownum + 1;
    output;
  end;
  else output;
run;

proc sort data=tmp2 out=want(drop=rownum);
  by descending rownum;
run;

Resulting table

Upvotes: 3

Related Questions