The SAS Output Delivery System and reporting techniques

Interleaving data for a report?

Reply
N/A
Posts: 0

Interleaving data for a report?

Hi

I would like to create a report where I list a reference first followed by data for comparison.
The report should look something like this.
[pre]City: New York

Gender Height Weight Age
Male 6.0 150 29

Tom 5.9 145 30
Bob 6.1 160 28

Gender Height Weight Age
Female 5.3 120 28

Kim 5.2 115 25
Amy 5.7 130 26

City: Los Angeles

Etc.[/pre]
The input data sets would look similar to this:
[pre]Dataset reference
City Gender Height Weight Age
New York Male 6.0 150 29
New York Female 5.3 120 28
Los Angeles ...

Dataset comparison
City Name Gender Height Weight Age
New York Tom Male 5.9 145 30
New York Bob Male 6.1 160 28
New York Kim Female 5.2 115 25
New York Amy Female 5.7 130 26[
Los Angeles ...[/pre]
I know the basics of proc report but I don't know if it's possible to interleave data in this way.

Best regards,
Thomas
SAS Super FREQ
Posts: 8,743

Re: Interleaving data for a report?

Hi:
You'd have to use a COMPUTE block with PROC REPORT, but this is a do-able report. The headers might not look exactly as you have shown them -- because PROC REPORT wants to only put the header at the top of the table and the top of a page. You might like the look of the headers better if you used CITY for BY group processing inside PROC REPORT.

Just curious -- in the normal way that things work (alphabetically), the 'L' in Los Angeles would come before the 'N' in New York and the 'F' for Female would come before the 'M' for Male. So do you have some other kind of sort order in mind???

cynthia
N/A
Posts: 0

Re: Interleaving data for a report?

I didn't think about the sort order in my example. Sorry for the confusion.

I'll look into compute blocks. Thanks for your help Smiley Happy

Best regards,
Thomas
SAS Super FREQ
Posts: 8,743

Re: Interleaving data for a report?

Hi:
You will use a COMPUTE BEFORE...either COMPUTE BEFORE CITY or COMPUTE BEFORE _PAGE_ (if you have/want every city to start a separate page). In this instance (COMPUTE BEFORE), you would have a NOPRINT option on the DEFINE statement for CITY and CITY would need to be an ORDER usage report item. In addition, you will use a LINE statement to actually write the value of CITY at the top of the ROWS for that CITY. Something like this (untested code), which assumes that your variables are CITY, GENDER, NAME, HEIGHT, WEIGHT and AGE...and that you want the MEAN statistic for the GENDER summary line for each group:
[pre]
ods html file='compute_before_noby.html' style=sasweb;

proc report data=your_data nowd;
column city gender name height weight age;
define city / order noprint;
define gender / order;
define name / order;
define height /mean;
define weight / mean;
define age / mean;
break before gender / summarize;
compute before city/
style={just=l};
cline = 'City: '||trim(city);
clg = length(cline);
line cline $varying. clg;
endcomp;
run;

ods html close;
[/pre]

But an alternative to this approach would be to use BY CITY inside PROC REPORT and then to use the CITY name in the TITLE statement. However, this would mean that every CITY would be placed in a separate table (and if you were using ODS RTF or ODS PDF -- every table would start on a separate page). But this would also allow the use of #BYVALn syntax in the TITLE statement, which would take the place of the COMPUTE BEFORE block. Something like this:
[pre]
proc sort data=your_data;
by city;
run;

options nobyline;
ods html file='use_break_and_by.html' style=sasweb;

proc report data=your_data nowd;
column city gender name height weight age;
by city;
title j=c 'City: #byval1';
define city / order noprint;
define gender / order;
define name / order;
define height /mean;
define weight / mean;
define age / mean;
break before gender / summarize;
run;

ods html close;
options byline;
title;
[/pre]

So it's partly going to be a trade-off between how it looks with COMPUTE BEFORE CITY, versus COMPUTE BEFORE _PAGE_ versus using a BY statement and how well you understand PROC REPORT syntax or how much learning about PROC REPORT syntax you want to do.

These are some good papers on PROC REPORT:
http://support.sas.com/resources/papers/ProcReportBasics.pdf
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
http://support.sas.com/resources/papers/proceedings10/133-2010.pdf
http://support.sas.com/resources/papers/proceedings10/141-2010.pdf
http://www2.sas.com/proceedings/sugi30/244-30.pdf
http://www.caloxy.com/papers/65_HOW07.pdf
http://support.sas.com/resources/papers/proceedings09/026-2009.pdf

cynthia
N/A
Posts: 0

Re: Interleaving data for a report?

Hi,

Unfortunately what I'm looking for isn't a summary line. Instead of the summary line I need the report to show the data from the "reference" dataset. I've looked at the linked papers but it isn't clear to me if it's possible to do something like that.

Perhaps my example wasn't well thought out. The actual report I'm hoping to do is about interests on bank accounts. So for each region (city in my example) and each type of account (gender in my example) I want at first to show what a standard account looks like followed by actual customer accounts that differ from the standard.

English isn't my first language so I had hoped I could explain what I'm hoping to achieve in everyday terms. Again I'm sorry for any confusion.

Best regards,
Thomas Message was edited by: Thomas@FIH
SAS Super FREQ
Posts: 8,743

Re: Interleaving data for a report?

Hi:
PROC REPORT will only process 1 dataset as input. So if you can "massage" your data to be in a structure that PROC REPORT can deal with, it would be possible to produce the report output that you showed. However, it sounds, in this case, like your data is coming from 2 different datasets -- and you are correct -- PROC REPORT will not "interleave" your 2 datasets in order to produce a report. You would essentially have to figure out a way to get your "standard account" for each region into the data.

This will become more of a DATA step/data manipulation exercise for you. I'm still not sure I understand what you mean by "reference" dataset -- but the bottom line is that PROC REPORT will only process 1 dataset at a time.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 367 views
  • 0 likes
  • 2 in conversation