The SAS Output Delivery System and reporting techniques

proc report repeat the group value for each lines

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

proc report repeat the group value for each lines

Hello,

 

by launching this proc report, the column AXE_PRODUIT is repeated as intended but it does not work for column generation and I don't know why. thanks a lot in advance for your help.

regard,Capture.PNG

Nasser

 PROC REPORT DATA = work.T7_MEASURES_M0 (where=(INDEX(Measure,'sans cumul'))) nowd ;

column AXE_PRODUIT GENERATION measure periode_month_M0 , value ;

define AXE_PRODUIT / group 'Produit' ;

COMPUTE AXE_PRODUIT ;

 IF AXE_PRODUIT NE '' then hold_axe_produit = AXE_PRODUIT ;

 IF AXE_PRODUIT EQ '' then AXE_PRODUIT = hold_axe_produit ;

ENDCOMP ;

define GENERATION / group 'Génération' ; 

COMPUTE GENERATION ;

 IF GENERATION NE '' then hold_GENERATION = GENERATION ;

 IF GENERATION EQ '' then GENERATION = hold_GENERATION ;

ENDCOMP ;

define measure / group 'Indicateur' ;

define periode_month_M0 / across '' ;

define value / '' ;

compute value ;

 if INDEX(measure,'Mont') then do ;

 call define(_COL_,'FORMAT','12.2') ;

end;

else call define(_COL_,'FORMAT','percent12.9') ;

endcomp ;

RUN ;


Accepted Solutions
Solution
‎11-02-2017 04:35 AM
SAS Super FREQ
Posts: 9,254

Re: proc report repeat the group value for each lines

[ Edited ]
Posted in reply to Nasser_alfea

Hi:

  You did not post any test data, so it is hard to comment in great detail. However, the code you're using looks like it was modeled on the automatic PROC REPORT code that is generated by Enterprise Guide. Although the automatic code uses OK techniques for most cases, in some instances, the code they generate needs to be modified. Example #1 in the screen shot below uses SASHELP.PRDSALE data to mimic the basics of your report and as you can see, the second column is not populated as you want. However, in Example #2, using an alternate technique (COMPUTE BEFORE), you can ALWAYS grab a group or order value at the break BEFORE the group and this is the most reliable way to create a temporary variable to hold the value of a group or order variable for use in a subsequent COMPUTE block.

 

  Then using the COMPUTED usage technique along with NOPRINT, you can NOPRINT the actual variables from the data and make "display" versions of the variables to appear on each row. Here's the difference in the 2 techniques:

use_diff_technique_correct.png

 

and here's the code that generated the above reports:

proc sort data=sashelp.prdsale out=prdsale;
by product year;
where quarter=1 and year=1993 and product in ('SOFA' 'CHAIR');
run;
  
PROC REPORT DATA = prdsale nowd ;
  title '1) code as posted -- problem with year column';
	column product year country actual,month ;
	define product / group 'Produit' ;
		COMPUTE product ;
		IF product NE '' then hold_product = product ;
		IF product EQ '' then product = hold_product ;
		ENDCOMP ;
	define year / group 'Génération' ; 
		COMPUTE year ;
		IF year NE . then hold_year = year ;
		IF year EQ . then year = hold_year ;
		ENDCOMP ;
	define country / group 'Indicateur';
	define month / across '' f=monname. order=internal;
	define actual / '' ;
RUN ;
  
PROC REPORT DATA = prdsale nowd ;
  title '2) Revised code using different technique';
	column product year disp_prod disp_year country actual,month ;
	define product / group 'Produit' noprint;
	define year / group 'Génération' noprint; 
	define disp_prod / computed 'Produit';
	define disp_year / computed 'Génération';
	define country / group 'Indicateur';
	define month / across '' f=monname. order=internal;
	define actual / '' ;
    compute before product;
	   hold_prod=product;
	endcomp;
	compute before year;
	   hold_year = year;
	endcomp;
	COMPUTE disp_prod / character length=20;
		disp_prod=hold_prod;
	ENDCOMP ;
	COMPUTE disp_year ;
		disp_year = hold_year;
	ENDCOMP ;

RUN ;

Hope this helps,

 

cynthia

View solution in original post


All Replies
Frequent Contributor
Posts: 111

proc report repeat the group value for each lines

Posted in reply to Nasser_alfea
PROC REPORT DATA = work.T7_MEASURES_M0 (where=(INDEX(Measure,'sans cumul'))) nowd ;
	column AXE_PRODUIT GENERATION measure periode_month_M0  , value ;
	define AXE_PRODUIT / group 'Produit' ;
		COMPUTE AXE_PRODUIT ;
		IF AXE_PRODUIT NE '' then hold_axe_produit = AXE_PRODUIT ;
		IF AXE_PRODUIT EQ '' then AXE_PRODUIT = hold_axe_produit ;
		ENDCOMP ;
	define GENERATION / group 'Génération' ; 
		COMPUTE GENERATION ;
		IF GENERATION NE '' then hold_GENERATION = GENERATION ;
		IF GENERATION EQ '' then GENERATION = hold_GENERATION ;
		ENDCOMP ;
	define measure /  group 'Indicateur' ;
	define periode_month_M0 / across '' ;
	define value / '' ;
		compute value ;
 		if INDEX(measure,'Mont') then do ;
 		call define(_COL_,'FORMAT','12.2') ;
 		end;
 		else call define(_COL_,'FORMAT','percent12.9') ;
 		endcomp ;
  	RUN ;

Capture.PNG

 

 

Hello,

 

by executing this proc report, the group "produit" is repeated for all lines as intended. but it does not work for the column "generation"

I don't understand why. thanks a lot in advance for your help

regards,

Nasser

Super User
Posts: 22,823

Re: proc report repeat the group value for each lines

Posted in reply to Nasser_alfea
Please don't post the same question multiple times. Note that I've also moved this to the ODS and Base Reporting section as it's more appropriate there and the relevant users will see the question. The forum appears to be having technical issues, so it may take a while for you to get an answer to this. In general, it also helps if you either include sample data so we can run/test your code or use a SASHELP data set.
Frequent Contributor
Posts: 111

Re: proc report repeat the group value for each lines

Hello Reeza,

 

sorry for that. thanks for your action.

Solution
‎11-02-2017 04:35 AM
SAS Super FREQ
Posts: 9,254

Re: proc report repeat the group value for each lines

[ Edited ]
Posted in reply to Nasser_alfea

Hi:

  You did not post any test data, so it is hard to comment in great detail. However, the code you're using looks like it was modeled on the automatic PROC REPORT code that is generated by Enterprise Guide. Although the automatic code uses OK techniques for most cases, in some instances, the code they generate needs to be modified. Example #1 in the screen shot below uses SASHELP.PRDSALE data to mimic the basics of your report and as you can see, the second column is not populated as you want. However, in Example #2, using an alternate technique (COMPUTE BEFORE), you can ALWAYS grab a group or order value at the break BEFORE the group and this is the most reliable way to create a temporary variable to hold the value of a group or order variable for use in a subsequent COMPUTE block.

 

  Then using the COMPUTED usage technique along with NOPRINT, you can NOPRINT the actual variables from the data and make "display" versions of the variables to appear on each row. Here's the difference in the 2 techniques:

use_diff_technique_correct.png

 

and here's the code that generated the above reports:

proc sort data=sashelp.prdsale out=prdsale;
by product year;
where quarter=1 and year=1993 and product in ('SOFA' 'CHAIR');
run;
  
PROC REPORT DATA = prdsale nowd ;
  title '1) code as posted -- problem with year column';
	column product year country actual,month ;
	define product / group 'Produit' ;
		COMPUTE product ;
		IF product NE '' then hold_product = product ;
		IF product EQ '' then product = hold_product ;
		ENDCOMP ;
	define year / group 'Génération' ; 
		COMPUTE year ;
		IF year NE . then hold_year = year ;
		IF year EQ . then year = hold_year ;
		ENDCOMP ;
	define country / group 'Indicateur';
	define month / across '' f=monname. order=internal;
	define actual / '' ;
RUN ;
  
PROC REPORT DATA = prdsale nowd ;
  title '2) Revised code using different technique';
	column product year disp_prod disp_year country actual,month ;
	define product / group 'Produit' noprint;
	define year / group 'Génération' noprint; 
	define disp_prod / computed 'Produit';
	define disp_year / computed 'Génération';
	define country / group 'Indicateur';
	define month / across '' f=monname. order=internal;
	define actual / '' ;
    compute before product;
	   hold_prod=product;
	endcomp;
	compute before year;
	   hold_year = year;
	endcomp;
	COMPUTE disp_prod / character length=20;
		disp_prod=hold_prod;
	ENDCOMP ;
	COMPUTE disp_year ;
		disp_year = hold_year;
	ENDCOMP ;

RUN ;

Hope this helps,

 

cynthia

Frequent Contributor
Posts: 111

Re: proc report repeat the group value for each lines

Posted in reply to Cynthia_sas

Hi Cynthia,

 

yes This realy helped me ! thanks a lot

have a good day

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 630 views
  • 0 likes
  • 3 in conversation