Calculating % from 2 different datasets 1

[cosmid]

Hi,

I have 2 data sets. Data set Participated and Emp. Participated has all the information of participated events. Emp has the total number of employees. My objective is to calculate the % of depts that participated in the event.

For example, There 5 different depts, dept1-dept5. The total number of employees for each dept are in data set Emp. The number of employees from each dept who participated are in data set Participate. If an employee from dept1 participated, then there would be a record existing in dataset Participate. If they did not participate, then there would be no records in data set Participate.

Data sets look like:

Participate: If the employee from Emp does not exist in this table that means that employee did not participate

empId dept
1234 1
1111 2
1211 2

Emp: Lists the employees and their deptid

empID dept
1234 1
1111 2
1231 3
1211 2

I need to come up with something like:
deptid dept(from set1) dept(from set2) %
1 1 1 100%
2 2 2 100%
3 0 10 0%
4 1 5 20%

what would be the best way of doing this?

Thanks!

 

[dblan]

cosmid, this was my solution:

data Par;
input EmpID Dept;
datalines;
1234	1
1111	2
1211	2
;

data Emp;
input EmpID Dept;
datalines;
1234     1
1111     2
1231     3
1211     2
1214     2
;
run;

proc sql;
create table Pct_Par as
select a.Dept, b.ParCt, a.EmpCt, (b.ParCt / a.EmpCt) as Par_Pct

from (select Dept, Count(EmpID) as EmpCt
	  from Emp
	  group by 1) as A

left join (select Dept, Count(EmpID) as ParCt
		   from Par
		   group by 1) as B
on a.Dept = b.Dept;
quit;

Results from final table:

Dept  ParCt  EmpCt  Par_Pct
1	  1	  1	  1
2	  2	  3	  0.66667
3	  .	  1       .

 

[cosmid]

Thanks for the code. Much more efficient than the one I wrote using Proc SQL.

But is there any solutions that doesn't involve Proc SQL? Will any basic SAS proc that can do this? My supervisor isn't too happy about me using proc SQL on my code.

 

[dblan]

Proc SQL tends to offer a more streamlined method to summarizing data, so it is a favorite of mine.

Here is how you could pull it off without Proc SQL:

proc sort data=emp;
by dept;
proc sort data=par;
by dept;
proc summary data=emp;
by dept;
output out=emp_sum;
proc summary data=par;
by dept;
output out=par_sum;
run;

data merged (drop=_type_);
merge emp_sum (rename=(_freq_=EmpCt))
	  par_sum (rename=(_freq_=ParCt));
Par_Pct = ParCt/EmpCt;
run;

 

[cosmid]

Ahh...thanks! Really appreciate this!