Desktop productivity for business analysts and programmers

SAS EG simple query on a calculated field.pls help

Reply
New Contributor
Posts: 4

SAS EG simple query on a calculated field.pls help

I have to calculate one field where by it will give me percentage of one amount column when compare to the whole amount field and then the final output as a pivot. (refer the attachment of xls pivot .png file attached)

 

Raw Data 
bus_descExposure
marketing100
marketing200
sales300
sales350
HR1000
HR400

 

One field computation in xls format with formulae  (column name 'Computed_percetange_eachExp_sum' shown for first row)

 

bus_desc(A)Exposure (B)Computed_percetange_eachExp_sum
marketing100(100*B1/SUM(B$1:B$4))
marketing2008.51
sales30012.77
sales35014.89
HR100042.55
HR40017.02

 

and finally the output required in SAS EG (output computed in xls pivot option

final pivot table in xls.png

Super User
Posts: 10,530

Re: SAS EG simple query on a calculated field.pls help

data have;
input bus_desc $ Exposure;
cards;
marketing 100
marketing 200
sales 300
sales 350
HR 1000
HR 400
;
run;

proc sql;
create table int as select bus_desc, sum(exposure) as exposure
from have group by bus_desc;
create table want as
select
  bus_desc,
  exposure,
  100 * exposure/sum(exposure) as percentage
from int;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,333

Re: SAS EG simple query on a calculated field.pls help

Posted in reply to KurtBremser

Note that you can implement both of @KurtBremser's SQL queries using the Query Builder in Enterprise Guide. No need to use SAS code.

Super User
Posts: 10,530

Re: SAS EG simple query on a calculated field.pls help


@TomKari wrote:

Note that you can implement both of @KurtBremser's SQL queries using the Query Builder in Enterprise Guide. No need to use SAS code.


Absolutely. But it took me less time to write that code than I would have needed in the Query Builder.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,333

Re: SAS EG simple query on a calculated field.pls help

Posted in reply to KurtBremser

Yes, @KurtBremser. I'm often amused that it takes next to no time to type out the SAS code to solve a problem, but to describe how to do something with the EG point-and-click facilities takes FOREVER!

 

Tom

New Contributor
Posts: 4

Re: SAS EG simple query on a calculated field.pls help

Posted in reply to KurtBremser

Thanks however I am working on EG and require EG screenshots or steps. Below is the code which can help in Base SAS..

Super User
Posts: 10,530

Re: SAS EG simple query on a calculated field.pls help

You have to build two queries in succession, just as I did in my code.

In the first, select both columns, and apply a SUM to exposure; bus_desc will be automatically used as group.

Build another query on the result of the first query, where you select both columns resulting from query #1, and add a computed column. Select advanced for that column, and enter the formula for the percentage (use the correct variable name for the column created in the first step). De-select automatically created groups, and you're done.

All that's left is some prettying up (proper column names etc.).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

Re: SAS EG simple query on a calculated field.pls help

Posted in reply to KurtBremser

If you are comfortable with SAS EG tool, can  you pls send the screenshots..it will be of great help

PROC Star
Posts: 618

Re: SAS EG simple query on a calculated field.pls help

Your pivot table results doesn't match what actually result. Actual results are:

image.png

data have;
input bus_desc :$20. Exposure;
datalines;
marketing 100
marketing 200
sales 300
sales 350
HR 1000
HR 400
;
run;


proc sql;
create table compute1 as
select 1 as group,bus_desc,Exposure,(100*Exposure/sum(Exposure)) as Computed_percetange_eachExp_sum
from have
group by 1;
run;

proc sql;
create table comp2(drop=row) as 
select *,(Sum_Exp/(select sum(Exposure) from have))*100 as Sum_Pct
		from (select monotonic() as row,bus_desc,sum(Exposure) as Sum_Exp
					from Compute1
						group by 2
				union 
				select monotonic()+10 as row,'Grand Total' as bus_desc,sum(Exposure) as Sum_Exp
					from Compute1
					)

order by 1
;
quit;

 

 

Thanks,
Suryakiran
New Contributor
Posts: 4

Re: SAS EG simple query on a calculated field.pls help

Hi friends.. thank you for your support... Anyways I cracked it on my own. It was very easy and need not have to write some codes..

Ask a Question
Discussion stats
  • 9 replies
  • 148 views
  • 1 like
  • 4 in conversation