BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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 🙂

Best regards,
Thomas
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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

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
  • 5 replies
  • 1369 views
  • 0 likes
  • 2 in conversation