topic Re: SAS EG simple query on a calculated field.pls help in SAS Enterprise Guide

SAS EG simple query on a calculated field.pls help

Tajinder — Tue, 10 Jul 2018 13:20:02 GMT

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_desc	Exposure
marketing	100
marketing	200
sales	300
sales	350
HR	1000
HR	400

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
marketing	100	(100*B1/SUM(B$1:B$4))
marketing	200	8.51
sales	300	12.77
sales	350	14.89
HR	1000	42.55
HR	400	17.02

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

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

Kurt_Bremser — Tue, 10 Jul 2018 13:57:54 GMT

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;

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

SuryaKiran — Tue, 10 Jul 2018 14:28:50 GMT

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

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;

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

TomKari — Tue, 10 Jul 2018 15:07:24 GMT

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.

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

Kurt_Bremser — Wed, 11 Jul 2018 06:27:51 GMT

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

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

TomKari — Wed, 11 Jul 2018 12:24:08 GMT

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

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

Tajinder — Wed, 11 Jul 2018 12:26:53 GMT

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

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

Kurt_Bremser — Wed, 11 Jul 2018 13:08:52 GMT

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

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

Tajinder — Wed, 11 Jul 2018 13:13:37 GMT

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

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

Tajinder — Thu, 12 Jul 2018 09:24:18 GMT

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