Help using Base SAS procedures

proc report computed variable by multiply 2 columns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

proc report computed variable by multiply 2 columns

PROC REPORT DATA = work.T_AMO_CRD_SORTIES nowd ;
	column MOIS_AMO MNT_MOIS_AMO_AVT_SORTIE TX_SORTIE_DDT MNT_MOIS_AMO_SORTIE_DDT , MOIS_PROD_LABEL ;
	define MOIS_AMO / group ;
	define MOIS_PROD_LABEL / across ''  ;
	define MNT_MOIS_AMO_AVT_SORTIE / display noprint;
	define TX_SORTIE_DDT / display noprint;
	define MNT_MOIS_AMO_SORTIE_DDT  / computed f = numx15.5 ;
	COMPUTE MNT_MOIS_AMO_SORTIE_DDT ;
		MNT_MOIS_AMO_SORTIE_DDT = MNT_MOIS_AMO_AVT_SORTIE * TX_SORTIE_DDT ;
		ENDCOMP ;
	title ;
  	RUN ;

Hello,

 

I get 2 columns amount ( )and percentage ( ) and I do not succeed to multiply them.

thanks in advance for your help

Nasser


Accepted Solutions
Solution
‎09-05-2017 11:16 AM
SAS Super FREQ
Posts: 825

Re: proc report computed variable by multiply 2 columns

[ Edited ]
Posted in reply to Nasser_alfea

Since Proc REPORT builds a report lione by line, we can no longer distingiush the values for the different drivetrains.

 

To get around this, you also need the horsepower and cylinders in your across defintion, so you can do the calculation.

 

For across columns, we can not use the varibale name, but have to use the absolute column number, including the ones that have the NOPRINT option set. See code below:

 

proc report data=newCars;
  column type horsepower Cylinders driveTrain, (horsePower=hp2 cylinders=cyl2 compVar) _dummy;
  define type / group;
  define driveTrain / across;
  define horsepower / analysis ;
  define Cylinders / analysis;
  define compVar / computed f=commax15.;
  define hp2 / analysis noprint;
  define cyl2 / analysis noprint;
  define _dummy / computed noprint;

  compute _dummy;
     _c6_ = _c4_ * _c5_;
     _c9_ = _c7_ * _c8_;
     _c12_ = _c10_ * _c11_;
  endcomp;

run;

Depending on your data, this might not be the most flexible and maintenance friendly way of doing it.

 

Maybe it is better to precalculate all the data, and use Proc REPORT for the display/layout part, see example below:


proc sql;
  create table carsAggr as
  select
    type
    , driveTrain
    , sum(horsePower) as sumhp
    , sum(cylinders) as sumcyl
    , calculated sumhp * calculated sumcyl as mult_hp_cyl
  from
    sashelp.cars
  group by
    type
    , driveTrain
  ;
quit;

proc report data=carsAggr;
  column type sumhp sumcyl mult_hp_cyl, driveTrain;
  define type / group;
  define driveTrain / across;
  define sumhp / analysis f=commax15.;
  define sumcyl / analysis f=commax15.;
  define mult_hp_cyl / analysis f=commax15.;
run;

 

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 825

Re: proc report computed variable by multiply 2 columns

Posted in reply to Nasser_alfea

Hi

 

You can not use GROUP and DISPLAY together. I assume you get a note like:

NOTE: Groups are not created because the usage of ..... is DISPLAY. To avoid this note, change all GROUP variables to ORDER variables.

 

Testing this out with data all SAS users have access to to makes things easier.

 

The following code has two examples, first using DISPLAY, so every row will be displayed, the second using ANALYSIS, where data is aggregated on the group varibale. Which one represents waht you need?

proc report data=sashelp.cars(obs=20);
  column type horsepower Cylinders compVar, driveTrain ;
  define type / group;
  define driveTrain / across;
  define horsepower / display ;
  define Cylinders / display;
  define compVar / computed f=commax15.;

  compute compVar;
     compVar = horsePower * Cylinders;
  endcomp;

run;

proc report data=sashelp.cars;
  column type horsepower Cylinders compVar, driveTrain ;
  define type / group;
  define driveTrain / across;
  define horsepower / analysis ;
  define Cylinders / analysis;
  define compVar / computed f=commax15.;

  compute compVar;
     compVar = horsePower * Cylinders;
  endcomp;

run;

 

Frequent Contributor
Posts: 117

Re: proc report computed variable by multiply 2 columns

Posted in reply to Bruno_SAS

Thanks Bruno,

 

proc report computed.PNG

But  I do not manage to display the computed varaible datas. even if with the example you gave to me. you can notice it in the encmosed screenshot

Solution
‎09-05-2017 11:16 AM
SAS Super FREQ
Posts: 825

Re: proc report computed variable by multiply 2 columns

[ Edited ]
Posted in reply to Nasser_alfea

Since Proc REPORT builds a report lione by line, we can no longer distingiush the values for the different drivetrains.

 

To get around this, you also need the horsepower and cylinders in your across defintion, so you can do the calculation.

 

For across columns, we can not use the varibale name, but have to use the absolute column number, including the ones that have the NOPRINT option set. See code below:

 

proc report data=newCars;
  column type horsepower Cylinders driveTrain, (horsePower=hp2 cylinders=cyl2 compVar) _dummy;
  define type / group;
  define driveTrain / across;
  define horsepower / analysis ;
  define Cylinders / analysis;
  define compVar / computed f=commax15.;
  define hp2 / analysis noprint;
  define cyl2 / analysis noprint;
  define _dummy / computed noprint;

  compute _dummy;
     _c6_ = _c4_ * _c5_;
     _c9_ = _c7_ * _c8_;
     _c12_ = _c10_ * _c11_;
  endcomp;

run;

Depending on your data, this might not be the most flexible and maintenance friendly way of doing it.

 

Maybe it is better to precalculate all the data, and use Proc REPORT for the display/layout part, see example below:


proc sql;
  create table carsAggr as
  select
    type
    , driveTrain
    , sum(horsePower) as sumhp
    , sum(cylinders) as sumcyl
    , calculated sumhp * calculated sumcyl as mult_hp_cyl
  from
    sashelp.cars
  group by
    type
    , driveTrain
  ;
quit;

proc report data=carsAggr;
  column type sumhp sumcyl mult_hp_cyl, driveTrain;
  define type / group;
  define driveTrain / across;
  define sumhp / analysis f=commax15.;
  define sumcyl / analysis f=commax15.;
  define mult_hp_cyl / analysis f=commax15.;
run;

 

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 237 views
  • 0 likes
  • 2 in conversation