The SAS Output Delivery System and reporting techniques

Best practice for creating "specific" proc report?

Reply
Regular Contributor
Posts: 161

Best practice for creating "specific" proc report?

Hi all,

I need some help with building more-less specific proc report.

Here is input data:

Idab
110200
110350
110390
125400
210200
215200
215200
235300


Target report  should creates from input data set using such rules:

1."Id" column directly maps to target report.

2. If ,for same "id" value, "a" value is the same for a few sequent rows - all these cells should be merge into one cell with this one value on center.

3. If ,for same "id" value, "a" value in current row is different then in previos - new value in this current cell="a" value from current row - "a" value from previous row .

4. For column "b" same rulues as for "a".

So using these simple rules target report should looks like:

Idab
110200
1150
140
11510
210200
25
2
220100

Actually I started implement this task by adding additional functionality for input data set(retain a few columns, compare them to actual processed columns , create some flags columns using this info) but I have strong feeling that this task can be realize easier, without using pre-calculated aditional columns in input data step,using only proc report features.

So shortly I'd like to know what is the best practice for implementing such type of reports?

Thanks!

Super Contributor
Posts: 276

Re: Best practice for creating "specific" proc report?

Hi..

Your Question was bit interesting but i think you can not do such data calculations in Proc Report, specially your 3rd rule.(Comparing rows).

Any way lets wait for official comments from SAS people on your question.me too eagerly  waiting for solution from SAS experts..

Thanks&Regards..

Sanjeev.K

Regular Contributor
Posts: 161

Re: Best practice for creating "specific" proc report?

Hi Sanjeev,

Actually implement these few rules very eazy from data step:

data ReportData(keep=id aNew bNew);

retain idPrev aPrev bPrev;

set testData;

by id;

if first.id then do;

  aNew=a;

  aPrev=a;

  bNew=b;

  bPrev=b;

  idPrev=id;

  output;

end;

else do;

  if id=idPrev  then do;

   if a=aPrev then do;

    aNew=.;

   end;

   else do;

    aNew=a-aPrev;

    aPrev=a;

   end;

   if b=bPrev then do;

    bNew=.;

   end;

   else do;

    bNew=b-bPrev;

    bPrev=b;

   end;

   output;

  end;

  else do;

   idPrev=id;

  end;

end;

run;

In realization higher I just set to missing values that are the same in previous row for same id.

But again, I need to merge such cells in proc report into one cell(as usual Excel merge cells) plus put needed value(that are the same in these cells) in mergerd cell center...

So data step higher isn't exactly what I need...

Thanks!

SAS Super FREQ
Posts: 8,814

Re: Best practice for creating "specific" proc report?

Hi:

  It looks to me like your column B, as originally shown is some kind of cumulative number. Your problem -- making a decision and then subtracting both A and B to get a "new" value for both A and B does not seem like something that PROC REPORT could do easily for you. I believe that you will have to manipulate the data in a DATA step program in order to derive "new" A and "new" B according to your program logic.

  However, I don't know what you mean by "merge" cells into one cell. Generally, PROC REPORT does not do the same kind of "merging" that you can do in Excel. However, there is the SPANROWS command that will do some merging for GROUP or ORDER variables that have the same value. Although placing a "line" underneath the spanned rows might not be the easiest thing to do, if it has to be done based on some arbitrary condition.

  But what you want to do doesn't really make sense to me either. I don't understand the point of arbitrarily subtracting previous values of A from current values of A or previous values of B from current values of B. It seems an exercise in DATA step processing using FIRST. or LAG rather than a report requirement.

cynthia

Ask a Question
Discussion stats
  • 3 replies
  • 282 views
  • 0 likes
  • 3 in conversation