BookmarkSubscribeRSS Feed
Tajinder
Calcite | Level 5

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

9 REPLIES 9
Kurt_Bremser
Super User
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;
TomKari
Onyx | Level 15

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

Kurt_Bremser
Super User

@TomKari wrote:

Note that you can implement both of @Kurt_Bremser'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.

TomKari
Onyx | Level 15

Yes, @Kurt_Bremser. 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

Tajinder
Calcite | Level 5

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

Kurt_Bremser
Super User

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.).

Tajinder
Calcite | Level 5

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

SuryaKiran
Meteorite | Level 14

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
Tajinder
Calcite | Level 5

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..

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 1278 views
  • 1 like
  • 4 in conversation