topic Re: How to fill the repetitive value in Pro Tabulate statement in SAS Programming

How to fill the repetitive value in Pro Tabulate statement

LL5 — Tue, 07 Jun 2016 15:00:01 GMT

FROM THE RESULT TABLE BELOW, HOW TO FILL THE REPETITIVE VALUE IN COLUMN A IN THE PRO BABULATE STATEMENT? IN THIS EXAMPLE, THE REPETITIVE VALUE WOULD BE "NEW YORK" IN FROM CELL A4 TO A7.

PROC TABULATE DATA=DATABASE.NY ;

CLASS STATE CITY;

VAR PROFIT EXPENSE;

TABLE (STATE=""*(CITY="")ALL="TOTAL"),PROFIT EXPENSE

/BOX="STATE";

RUN;

	A	B	C	D
1	STATE		PROFIT	EXPENSE
2			Sum	Sum
3	NEW YORK	NEW YORK CITY	2091	558
4		BROOKLYN	304	909
5		STATEN ISLAND	1127	615
6		BRONX	2285	460
7		QUEEN	1143	827
	TOTAL		6950	3369

Re: How to fill the repetitive value in Pro Tabulate statement

ballardw — Tue, 07 Jun 2016 16:03:21 GMT

Proc tabulate doesn't have an option for that.

You may consider creating an output data set from Tabulate and then manipulating that set and then print the result. Or perhaps proc report.

Re: How to fill the repetitive value in Pro Tabulate statement

Cynthia_sas — Tue, 07 Jun 2016 16:37:35 GMT

Hi:
In addition to BallardW's suggestions, you can use PROC REPORT to get what you want.
cynthia

Re: How to fill the repetitive value in Pro Tabulate statement

LL5 — Tue, 07 Jun 2016 17:11:19 GMT

Thanks Ballard and Cynthia. How to use Proc Report to do that?

Apperciated for your helps!

Re: How to fill the repetitive value in Pro Tabulate statement

Cynthia_sas — Wed, 08 Jun 2016 16:35:40 GMT

Hi:

This is the default behavior from PROC REPORT:

You can make a COMPUTED item in PROC REPORT to "copy" the variable of interest (in my example, PRODUCT) down all the other report rows for the group. This is the output produced by the program below.

There is also an additonal #3 example that shows adding a summary line under each group.

cynthia


** make small subset;
proc sort data=sashelp.shoes out=newshoes;
  by product region;
  where product in ('Boot' 'Slipper') and
        region in ('Asia' 'Canada' 'Western Europe');
run;
  
ods html file='c:\temp\fill_rows_example.html';
proc report data=newshoes;
  title '1) default behavior';
  column product region sales inventory returns;
  define product / group f=$14.;
  define region / group;
run;
   
proc report data=newshoes;
  title '2a) fill each row example using a COMPUTED item';
  column product showprod region sales inventory returns;
  define product / group f=$14.;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;
   
proc report data=newshoes;
  title '2b) final report -- using NOPRINT for Group var PRODUCT';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;

proc report data=newshoes;
  title '3) With Summary Line';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  break after product / summarize style=Header;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
	if upcase(_break_) = 'PRODUCT' then
	  showprod = catx(' ', showprod,'Total');
  endcomp;
run;

ods html close;
title;

Re: How to fill the repetitive value in Pro Tabulate statement

LL5 — Tue, 14 Jun 2016 01:40:16 GMT

Thanks cynthia!