Hi all,
I need some help with building more-less specific proc report.
Here is input data:
Id | a | b |
1 | 10 | 200 |
1 | 10 | 350 |
1 | 10 | 390 |
1 | 25 | 400 |
2 | 10 | 200 |
2 | 15 | 200 |
2 | 15 | 200 |
2 | 35 | 300 |
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:
Id | a | b |
1 | 10 | 200 |
1 | 150 | |
1 | 40 | |
1 | 15 | 10 |
2 | 10 | 200 |
2 | 5 | |
2 | ||
2 | 20 | 100 |
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!
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
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.