BookmarkSubscribeRSS Feed
CarlFritz
Calcite | Level 5
I am a new user of Proc Report and am not sure if what I am trying to do is possible. I am trying to make a simple table with a separate column for each value of the variable 'product' and the entries in each column beneath the product names are values of a character variable. The final table is intended to have one row for each attribute and one column for each product. Is it possible to put character values in columns under an ACROSS variable?
A portion of the data (with formats applied) and the SAS code I am using is shown below. The error message that I do not understand is shown at the bottom:

Data: (dots were inserted to improve readability)
Obs......attribute.............product......entry
1... Amt of Coating...Product 325...5/67/28
2... Amt of Coating...Product 559...14/68/19
3... Amt of Coating...Product 671...6/62/32
4... Amt of Coating...Product 834...6/58/36
5... Flavor Strength...Product 325...7/44/49
6... Flavor Strength...Product 559...11/53/36
7... Flavor Strength...Product 671...11/44/45
8... Flavor Strength...Product 834...6/44/50
9... Spiciness........Product 325...7/39/54
10... Spiciness.......Product 559...12/44/44
11... Spiciness.......Product 671...13/42/45
12... Spiciness.......Product 834... 9/34/57

proc report data=jar nowd;
column attribute product,entry;
define attribute / format=attrfmt. 'Attribute Name';
define product / across format=$sampfmt. 'Product' ;
define entry / display ;
run;

ERROR: A DISPLAY or GROUP variable above or below an ACROSS variable requires that there be an ORDER, GROUP, or DISPLAY variable in the report that is not above or below an ACROSS variable.
1 REPLY 1
Cynthia_sas
SAS Super FREQ
Hi, Carl:
If you run this version of your program:
[pre]
options nocenter;
proc report data=jar nowd;
column attribute product,entry;
define attribute / order 'Attribute Name';
define product / across 'Product' ;
define entry / display ;
run;
[/pre]
then you will get this (shown in LISTING)
[pre]
Product
Product Product Product Product
325 559 671 834
Attribute Name entry entry entry entry
Amt of Coating 5/67/28
14/68/19
6/62/32
6/58/36
Flavor Strength 7/44/49
11/53/36
11/44/45
6/44/50
Spiciness 7/39/54
12/44/44
13/42/45
9/34/57

[/pre]
which is probably CLOSE to what you want.
But I think you described a table where there was only 1 row per attribute value (which would mean the rows were collapsed -- or summarized in PROC REPORT terms). PROC REPORT does this kind of table because there are 4 observations for every one of your attribute values -- and ORDERing allows the ACROSS to work, but does not do collapsing of rows.
So, when you try to change the DEFINE statement for ATTRIBUTE from ORDER to GROUP:
[pre]
define attribute / group 'Attribute Name';
[/pre]
you will get this error message in the log: ERROR: There is no statistic associated with entry.
Because using GROUP to do the collapsing of the rows implies that you want the values to be "added" up or summarized.

So, what you can do instead might not need PROC REPORT at all. Assuming that all of your data falls into the same pattern, then you could bash it around with a DATA step program, put it into an array and create 4 numbered variables, say ENT1-ENT4, where ENT1 represented Product 325, and ENT2 represented Product 559 and ENT3 represented 671, etc, etc.

First, let's make you some data:
[pre]
data jar;
length attribute $15 product $11 entry $10;
infile datalines dsd;
input attribute $ product $ entry $;
return;
datalines;
"Amt of Coating","Product 325","5/67/28"
"Amt of Coating","Product 559","14/68/19"
"Amt of Coating","Product 671","6/62/32"
"Amt of Coating","Product 834","6/58/36"
"Flavor Strength","Product 325","7/44/49"
"Flavor Strength","Product 559","11/53/36"
"Flavor Strength","Product 671","11/44/45"
"Flavor Strength","Product 834","6/44/50"
"Spiciness","Product 325","7/39/54"
"Spiciness","Product 559","12/44/44"
"Spiciness","Product 671","13/42/45"
"Spiciness","Product 834","9/34/57"
;
run;
[/pre]

Now, let's abandon your original PROC REPORT and look at making an array of variables to hold your entry values. The program below makes a data set AND then 2 different reports
1) using DATA step array info while still INSIDE the DATA step program
2) using PROC REPORT on the new dataset from #1
[pre]
options nodate nonumber;

ods html file='c:\temp\newjar.html' style=sasweb;
data newjar(keep=attribute prod1-prod4 ent1-ent4);
title 'Create Data set and Report with DATA Step';
** do not really need prod1-prod4 in this case;
** but keep them here to show how the product number;
** could also be captured in an array;
length attribute $15 prod1-prod4 $11 ent1-ent4 $10;
retain i prod1-prod4 ent1-ent4;
set jar;
by attribute;
array prod $11 prod1-prod4;
array ent $10 ent1-ent4;
if first.attribute then do;
i = 1;
end;
prod(i) = trim(scan(product,2,' '));
ent(i) = entry;
i = i+1;
if last.attribute then do;
output;
i=0;
file print ods=(variables=(attribute ent1-ent4));
put @1 attribute @2 ent1 @3 ent2 @4 ent3 @5 ent4;
end;
label Attribute = 'Attribute'
ent1 = 'Product 325'
ent2 = 'Product 559'
ent3 = 'Product 671'
ent4 = 'Product 834';
run;

proc report data=newjar nowd;
title 'Using New Data Set with PROC REPORT';
title2 'But Without ACROSS';
column attribute ent1-ent4;
define attribute /order;
define ent1 / display 'Product 325';
define ent2 /display 'Product 559';
define ent3 / display 'Product 671';
define ent4 / display 'Product 834';
run;

proc print data=newjar;
title 'Data Set Created and Making Numbered variables';
run;
ods html close;
title;
[/pre]

I'm not going to describe in too much detail the inner workings of the DATA step program except to say that the program is "holding" or retaining new numbered variables in memory, while they are being populated (using the ARRAY logic). At the end of every attribute group, then and only then, is the new observation being written to the output data set. (see the final PROC PRINT). Also at the end of every attribute group, a FILE PRINT ODS statement is being used (along with the PUT statement) to actually write out a REPORT line. Report Column 1 is the attribute column, Report Column 2 is the ENT1 column, which I am labelling 'Product 325' (and labelling the other "ENT" variables with their PRODUCT numbers). So you are getting both a DATASET and a REPORT out of the DATA step program.

Next, the PROC REPORT step that follows is using the "NEWJAR" data file to generate the same report except with PROC REPORT syntax, using the new ENT1, ENT2, ENT3, ENT4 variables.

What you have to decide is whether the report that's coming out of the DATA _NULL_ program is OK for you. If it is, then you would not even need to use PROC REPORT. But, if you wanted (with ODS) to change some of the style information for the REPORT, this may be easier to do in a PROC REPORT step than in the DATA step program. So where yoiu go next sort of depends on what you want to do, ultimately, with the report.

If you need help understanding how to work with ARRAYs or change the DATA step program to suit your data (in case it might not be structured the same as my test data), your best bet for help is to contact Tech Support.

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
  • 1 reply
  • 708 views
  • 0 likes
  • 2 in conversation