Using SAS EG 7.1 and trying to make the variables repeat values in a summary table. It is currently only showing the first value.
Example:
What I am getting now
Origin | Destination | Cost |
New York | Florida | 200 |
Colorado | 638 | |
Kentucky | 194 |
What I want
Origin | Destination | Cost |
New York | Florida | 200 |
New York | Colorado | 638 |
New York | Kentucky | 194 |
You should describe how you are doing that summary. There a number of different ways to summarize data and we need a starting point. If you are using tasks you may be missing an option.
And are you creating a data set or a report?
I have my starting point whch is a data set that I have created and manipulated. Then I have selected Tasks > Describe > Summary Tables.
Data Tab:
Analysis Variable = Cost
Classification Variables = Origin, Destination
Summary Tables Tab:
Placed them where I want them.
Run.
And what is the sas program code generating these results?
TITLE;
TITLE1 "Summary Tables";
FOOTNOTE;
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
/* -------------------------------------------------------------------
Code generated by SAS Task
Generated on: Wednesday, September 27, 2017 at 3:40:58 PM
By task: Summary Tables (2)
Input Data: SASApp:WORK.QUERY_FOR_APPEND_TABLE_0001
Server: SASApp
------------------------------------------------------------------- */
/* -------------------------------------------------------------------
Run the tabulate procedure
------------------------------------------------------------------- */
PROC TABULATE
DATA=WORK.QUERY_FOR_APPEND_TABLE_0001
;
VAR Units "COST"n;
CLASS Origin / ORDER=UNFORMATTED MISSING;
CLASS Destination / ORDER=UNFORMATTED MISSING MLF;
TABLE /* Row Dimension */
'Origin'n*(
Destination
ALL={LABEL="Total (ALL)"}),
/* Column Dimension */
COST*
Sum;
;
RUN;
/* -------------------------------------------------------------------
End of task code
------------------------------------------------------------------- */
RUN; QUIT;
TITLE; FOOTNOTE;
Hi:
This is something you might want to try with the List Report task instead of Summary Tables task. Or else learn PROC REPORT code. This is easier to do with PROC REPORT than with TABULATE.
As an example -- the top table is PROC TABULATE on a subset of SASHELP.SHOES and the bottom table is the PROC REPORT TABLE showing the region on each row, as you want.
Here's the code:
title; footnote;
PROC TABULATE DATA=sashelp.shoes;
where product in ('Boot' 'Sandal' 'Slipper') and
region in ('Asia' 'Canada');
VAR sales;
CLASS region product ;
TABLE /* Row Dimension */
region*(product ALL={LABEL="Total (ALL)"}),
sales* Sum;
RUN;
proc report data=sashelp.shoes;
where product in ('Boot' 'Sandal' 'Slipper') and
region in ('Asia' 'Canada');
column region show_region product sales;
define region / group noprint;
define show_region/ computed 'Region' style(column)=Header;
define product / group style(column)=Header;
define sales / sum f=dollar14.;
break after region / summarize style=Header;
compute before region;
hold = region;
endcomp;
compute show_region / character length=30;
show_region = hold;
endcomp;
compute after region;
show_region = "Total (ALL)";
endcomp;
run;
Hope this helps,
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.