topic Re: Query on Merging in SAS Enterprise Guide

Query on Merging

Sean_OConnor — Thu, 17 Aug 2017 12:04:58 GMT

Folks,

Would anyone be able to lend an opinion to the following issue.

At present I have a dataset with company names which a an individual has worked for.

id	company_1	company_2	company_3	company_4	company_5
1	ggfh	fdsgd
2	gdgd	fhf	ff	hhh
3	ghjh	dfgdf
4	ffds
5	hhj	hf	hfh
6	ggd
7	khff	dhgh	fdhf	fhg	t

So person 1 has worked for 2 companies and 7 has worked for 5.

On another dataset I have the amount of employees these companies have.

So something like this

Company	Employees
khff	10
dhgh	15
fdhf	1
fhg	2
t	156

What I would like to to do is create a new dataset which looks like the first dataset presented here, but rather than having company names that each indvidual worked for it has the number of employees.

I'm not too sure how to go about this so any information would be great!

Re: Query on Merging

Ksharp — Thu, 17 Aug 2017 12:47:18 GMT

Is it what you are looking for ?

data have;
infile cards expandtabs truncover;
input id	(company_1	company_2	company_3	company_4	company_5 ) ($);
cards;
1	ggfh	fdsgd	 	 	 
2	gdgd	fhf	ff	hhh	 
3	ghjh	dfgdf	 	 	 
4	ffds	 	 	 	 
5	hhj	hf	hfh	 	 
6	ggd	 	 	 	 
7	khff	dhgh	fdhf	fhg	t
;
run;

data company;
infile cards expandtabs truncover;
input Company $	Employees ;
cards;
khff	10
dhgh	15
fdhf	1
fhg	2
t	156
;
run;
data want;
if _n_=1 then do;
 if 0 then set company;
 declare hash h(dataset:'company');
 h.definekey('company');
 h.definedata('Employees');
 h.definedone();
end;
 set have;
 array x{*} $ company_1-company_5;
 array y{*} v1-v5;
 do i=1 to dim(x);
  if not missing(x{i}) then do;	Employees=.;rc=h.find(key:x{i}); y{i}=Employees;end;
 end;
 drop i rc company Employees;
run;

Re: Query on Merging

Sean_OConnor — Thu, 17 Aug 2017 13:12:13 GMT

Ksharp - yes this is exactly what I'm looking for. Thank you very much.