The SAS Output Delivery System and reporting techniques

Working with _C?_ columns in PROC REPORT

Reply
N/A
Posts: 0

Working with _C?_ columns in PROC REPORT

if you have code like

PROC REPORT DATA=TEST NOWD MISSING;
COLUMN STATE INDUSTRY,INCOME ;
DEFINE STATE/GROUP;
DEFINE INDUSTRY/ACROSS;
DEFINE INCOME /ANALYSIS;
RBREAK AFTER / SKIP OL SUMMARIZE;
RUN;

The acrosss variables will display as _C2_, _C3_, .....

Can you work with these in a compute block? einther individually? or all of them together (industry,income)?

It is a general question. But, my particular application is that I want to divide the _RBEAK_ line for these varibles by the number of rows.
SAS Super FREQ
Posts: 8,864

Re: Working with _C?_ columns in PROC REPORT

Posted in reply to deleted_user
Hi:
You might want to look at the examples in Technical Report P-258 and in the Proc Report doc:
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/report-overview.htm

This example (below) shows how the numbers assigned to the across variables will change, depending on what you have crossed underneath the across variable. For example, in the first program, I have the income, the mean of income and the count all nested underneath the industry variable. In the second program, there are only 2 variables nested underneath the industry variable. You can see, when you run this program, that NOPRINT does not affect the column number that's assigned. I just used LINE statements instead of any calculations to show what was in each absolute column at the RBREAK.

There are SAS macro programs that you could write that would figure out the levels of the ACROSS variables so you don't have to hard code the column numbers, but for starting out, this is a good place.

For an example of using a macro to determine across levels, see this first paper (and some other papers of interest):
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
http://www2.sas.com/proceedings/sugi31/235-31.pdf
http://www2.sas.com/proceedings/sugi30/244-30.pdf
http://www2.sas.com/proceedings/sugi29/242-29.pdf

cynthia
[pre]
data test(keep=state industry income);
set sashelp.prdsale;
State=region;
Industry=prodtype;
Income=actual;
run;

ods listing;
options nocenter nodate nonumber;

PROC REPORT DATA=TEST NOWD MISSING;
title 'With 3 variables under Across and all showing';
COLUMN STATE INDUSTRY,(INCOME income=incmn n);
DEFINE STATE/GROUP;
DEFINE INDUSTRY/ACROSS;
DEFINE INCOME /ANALYSIS f=dollar10.;
define incmn / mean 'Avg';
define n / 'Count';
RBREAK AFTER / SKIP OL SUMMARIZE;
compute after;
lnvar1 = catx(' ','Avg Income for _c3_ is: ',put(_c3_,dollar12.));
lnvar2 = catx(' ','Avg Income for _c6_ is: ',put(_c6_,dollar12.));
line ' ';
line lnvar1 $50.;
line lnvar2 $50.;
line ' ';

line 'Total for _c2_: ' _c2_ dollar10. ' and total for _c5_ is: ' _c5_ dollar10.;
endcomp;
RUN;


PROC REPORT DATA=TEST NOWD MISSING;
title 'With 2 variables under across and one of them NOPRINT';
COLUMN STATE INDUSTRY,(INCOME income=incmn);
DEFINE STATE/GROUP;
DEFINE INDUSTRY/ACROSS;
DEFINE INCOME /ANALYSIS f=dollar10.;
define incmn / mean 'Avg' noprint;
RBREAK AFTER / SKIP OL SUMMARIZE;
compute after;
lnvar1 = catx(' ','Avg Income for Furniture is: ',put(_c3_,dollar12.));
lnvar2 = catx(' ','Avg Income for Office is: ',put(_c5_,dollar12.));
line ' ';
line lnvar1 $50.;
line lnvar2 $50.;
line ' ';

line 'Total for _c2_: ' _c2_ dollar10. ' and total for _c4_ is: ' _c4_ dollar10.;
endcomp;
RUN;
[/pre]
N/A
Posts: 0

Re: Working with _C?_ columns in PROC REPORT

Posted in reply to deleted_user
Ok! I looked at some of the documentation you referred me to and didn't find the answer. I will look at the rest in a little bit.

One of my related questions is how to specify the sort of the columns created by an
across variable.

In this code

PROC REPORT DATA=TEST NOWD MISSING;
COLUMN STATE INDUSTRY,INCOME ;
DEFINE STATE/GROUP;
DEFINE INDUSTRY/ACROSS;
DEFINE INCOME /ANALYSIS;
RBREAK AFTER / SKIP OL SUMMARIZE;
RUN;

The columns _c2_ to whatever will be sorted in ascending order by the value of industry. If I change it to

DEFINE INDUSTRY/ACROSS descending ;

The columns should appear in descending order left to right.

How can I specify an arbitary oreder.

I thought I could define an user defined format and use PRELOADED and COMPLETCOLS and it would be displayed in the order specified by the format.
But that doesn't happen. Am I doing something wrong?

If I had a table where the across variable was POSTAL_STATE. Is there any way to display the states in the order the entered the union and have the variables defined as an ACROSS variable?
SAS Super FREQ
Posts: 8,864

Re: Working with _C?_ columns in PROC REPORT

Posted in reply to deleted_user
Hi:
Ordering the ACROSS variable doesn't really have anything to do with the absolute column number. In most cases, order=data or order=internal will be the option you want. ORDER=FORMAT is in ASCENDING order of the formatted values (NOT the order you enter them into the FORMAT). In some cases, you may have to sort the data before the PROC REPORT step and/or create a dummy variable to guarantee the order you want.

The code below shows the default method, the descending method, the dummy variable for ordering method and 2 examples of order=format. If you had a LOT of values for the across variable and it turns out that the only method is some kind of external variable to ensure the order...along with a format, then you may want to explore the CNTLIN feature of PROC FORMAT, where you can build a format from a SAS dataset.

If you didn't find the answer to your across question in the doc or the papers I mentioned, then perhaps you can get more help from Tech Support.

cynthia

[pre]
ods html file='c:\temp\rptord.html' style=sasweb;

proc report data=sashelp.shoes nowd;
title '1) default order';
where region in ('Asia','Pacific', 'Canada');
column product region;
define product /group;
define region /across;
run;

proc sort data=sashelp.shoes out=shoes;
by descending region;
run;

proc report data=shoes nowd;
title '2) descending order';
where region in ('Asia','Pacific', 'Canada');
column product region;
define product /group;
define region /across order=data;
run;

data neword;
set sashelp.shoes;
if region = 'Asia' then rord = 2;
else if region = 'Pacific' then rord=1;
else if region = 'Canada' then rord = 3;
run;

proc format;
value rord 1='Pacific'
3='Canada'
2='Asia';
run;

proc report data=neword nowd;
title '3) manual order var';
where region in ('Asia','Pacific', 'Canada');
column product rord;
define product /group;
define rord /across f=rord. order=internal;
run;

proc format;
** number after value shows the format value order;
value $rgord 'Africa' = '3) Africa 1'
'Asia'='1) Asia 2'
'Pacific'='2) Pacific 3';
value $rgalt 'Asia'='Asia 1 '
'Pacific'='Pacific 2'
'Africa' = 'Africa 3';

run;

proc report data=sashelp.shoes nowd;
title '4a) $RGORD order=format means ASCENDING formatted value order';
title2 'NOT the order that the formats are specified in';
where region in ('Asia','Pacific', 'Africa');
column product region;
define product /group;
define region /across f=$rgord. order=formatted;
run;

proc report data=sashelp.shoes nowd;
title '4b) $RGALT order=format means ASCENDING formatted value order';
title2 'NOT the order that the formats are specified in';
where region in ('Asia','Pacific', 'Africa');
column product region;
define product /group;
define region /across f=$rgalt. order=formatted;
run;

ods html close;
[/pre]
N/A
Posts: 0

Re: Working with _C?_ columns in PROC REPORT

Posted in reply to deleted_user
OK!

I looked at this before I posted my message.

Your two tables have different column headings and do NOT have the same column heading in a different orders. This is an important distinction.

So while the two tables are close, you creating two different tables.

proc format; ** number after value shows the format value order;
value $rgord
'Africa' = '3) Africa 1'
'Asia' ='1) Asia 2'
'Pacific' ='2) Pacific 3';

value $rgalt 'Asia' ='Asia 1 '
'Pacific '='Pacific 2'
'Africa' = 'Africa 3';
run;

Your approach seems to work because your format $rgord. uses the leading 1,2,3 to sort the formats and to do that I gives a different column head.

The answer seems to be you cannot use a format to arrange the rows in a specific order without changing the name displayed on the column head?
Ask a Question
Discussion stats
  • 4 replies
  • 279 views
  • 0 likes
  • 2 in conversation