BookmarkSubscribeRSS Feed
Yura2301
Quartz | Level 8

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!

3 REPLIES 3
kuridisanjeev
Quartz | Level 8

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

Yura2301
Quartz | Level 8

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!

Cynthia_sas
SAS Super FREQ

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

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
  • 3 replies
  • 880 views
  • 0 likes
  • 3 in conversation