Desktop productivity for business analysts and programmers

Query on Merging

Reply
Contributor
Posts: 46

Query on Merging

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.

 

idcompany_1company_2company_3company_4company_5
1ggfhfdsgd   
2gdgdfhfffhhh 
3ghjhdfgdf   
4ffds    
5hhjhfhfh  
6ggd    
7khffdhghfdhffhgt

 

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 

 

CompanyEmployees
khff10
dhgh15
fdhf1
fhg2
t156

 

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!

Super User
Posts: 10,123

Re: Query on Merging

Posted in reply to Sean_OConnor

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;
Contributor
Posts: 46

Re: Query on Merging

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

Ask a Question
Discussion stats
  • 2 replies
  • 85 views
  • 0 likes
  • 2 in conversation