BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

 

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;

 

 ABCD
1STATE PROFITEXPENSE
2  SumSum
3NEW YORKNEW YORK CITY2091558
4 BROOKLYN304909
5 STATEN ISLAND1127615
6 BRONX2285460
7 QUEEN1143827
 TOTAL 69503369
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  This is the default behavior from PROC REPORT:

proc_report1.png

 

  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.

proc_report_fill_row_rest_output.png

 

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;

View solution in original post

5 REPLIES 5
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ
Hi:
In addition to BallardW's suggestions, you can use PROC REPORT to get what you want.
cynthia
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

Apperciated for your helps!

 

Cynthia_sas
SAS Super FREQ

Hi:

  This is the default behavior from PROC REPORT:

proc_report1.png

 

  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.

proc_report_fill_row_rest_output.png

 

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;
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
Thanks 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
  • 960 views
  • 4 likes
  • 3 in conversation