topic Calculating percentages from 2 different tables SAS EG in SAS Enterprise Guide

Calculating percentages from 2 different tables SAS EG

AK100 — Wed, 25 Mar 2020 11:17:11 GMT

Morning Lads,

I wanted to ask an urgent question about calculating percentages from 2 different tables. In the first picture you see the total measurements per month/section.

In the second picture you see a the total measurements per MatType per month per section.

Now I want to calculate the percentages of subtotal/total in a new table but if I join the tables i get the wrong totals. So I actually want 21 (from second table) / 112 (from first table) and 6 (from second table) / 52 (from first table).

Please note that I use the query builders most of the time instead of coding (also note that these are just screenshots, the tables are much much bigger.

Re: Calculating percentages from 2 different tables SAS EG

PaigeMiller — Wed, 25 Mar 2020 11:42:32 GMT

Now I want to calculate the percentages of subtotal/total in a new table but if I join the tables i get the wrong totals.

What is wrong when you do this? How are you doing it?

So I actually want 21 (from second table) / 112 (from first table) and 6 (from second table) / 52 (from first table).

I don't understand the part in red.

Re: Calculating percentages from 2 different tables SAS EG

Kurt_Bremser — Wed, 25 Mar 2020 11:48:37 GMT

As I told you in our private conversation, you need to deliver usable example data. And what you expect to get out of it.

Expand this for table 1:

data table1;
input sectie $ maand2 :date9. totaal;
format maand2 yymon7.;
datalines;
2632CT 01jul2019 112
;

and provide a similar step for table 2. It is not rocket science, and it helps you learn basic data step technique, which is essential for further progress in using SAS to its fullest.

Re: Calculating percentages from 2 different tables SAS EG

AK100 — Wed, 25 Mar 2020 11:55:27 GMT

hi Paige Miller,

Right now I'am joining the 2 tables together and make 1 table out of it, but that gives me the wrong totals. Which means the percentages are wrong to. After the join there is no distinction There is no distiction between SECTIE anymore so all the totals of JULY turn out to be 112, august turn out to be 161. This, while every month has 2 totals (because of 2 different SECTIE's)

Dbout the red part: 21 is DDM (subtotal, SECTIE 2632CT) from the second table that has to be dived by 112 from the first table (the total).
6 is DDZ (subtotal, SECTIE 2646BT) from the second table which has to be dived by 52 from the first table (the total). This has to be done for all of them of course, i just picked 2 random. Hope it clear like this?

Re: Calculating percentages from 2 different tables SAS EG

AK100 — Wed, 25 Mar 2020 12:12:24 GMT

Hi Kurt,

I would love to learn that of course on your way, and I will code it in the coming days and let you know how it looks. However, I though maybe someone had a short solution for now because I need the output for work.

Re: Calculating percentages from 2 different tables SAS EG

Kurt_Bremser — Wed, 25 Mar 2020 12:29:16 GMT

See this example:

data table1;
input sectie $ maand :date9. totaal;
format maand yymon7.;
datalines;
2632CT 01jul2019 112
;

data table2;
input sectie $ maand2 :date9. subtotaal mattype $;
format maand2 yymon7.;
datalines;
2632CT 01jul2019 22 DDM
;

proc sql;
create table want as
select
  t1.sectie,
  t1.maand,
  t2.subtotaal,
  t2.subtotaal / t1.totaal as percentage format=percent7.2
from table1 t1, table2 t2
where t1.sectie = t2.sectie and t1.maand = t2.maand2;
quit;