SAS Relational Database Problems

[sburnett06]

I'm in serious need of help. I just blew my Friday night by having to stay at work, and I'm no closer to being done. Below is a simplified example of the two datasets I need to merge (or somehow combine through arrays or any other method of your choosing). If some SAS wizard could help, I would be eternally grateful.

Database name: Firm

Firm Employee_1 Employee_2 .... Employee_10
x 5500 2 1
y 2 1 5500
...
z 5 1402 507

Database name: Employee

Employee Var1 Var2 ... Var25
1 75 Tim Fish
2 1055 Jane Hamster
...
5500 2 Bill Dog

Desired Output:
Firm Employee_1 ... Employee_10 Var1_1 ... Var25_10
x 5500 1 2 Fish
y 2 5500 1055 Dog
....


Basically each Firm (x,y...z) can have a relationship with up to 10 employees (at most, there can be less), with employee ID's taking values between 1 and 5500. Each employee is associated with 25 variables. What I need is to create 250 (10x25) new variables for each firm observation, denoted Variable(var1-25)_(employee1-10).

I'm not sure how to do this (obviously).

I'm sure it can be done with a series of arrays, proc transpose followed by first./last. sums, or very clever merging macro (that loops for each employee ID), but I've failed on each of these fronts. Doing it manually is not an option (the actual resultant dataset should be 880000 by 2400).

Any help, even vague and generalized code, would be of immense help. I've only been using SAS for a few weeks and have no programming experience, so I'm hoping the SAS gods will take pity on me.

 

[klaz2002]

Sorry you had to lose a friday night. I just noticed this question today. First if are going to merge data you need to establish a key. This is a value that you can use to merge the many datasets that house your data.

I don't think that SAS will be that efficient with a dataset of that size. Perhaps you need to reevaluate what you need done. What kind of report do you need to create? If its stats you need done on your data this structure you mapped out is very inefficient.

Please explain your need, not how you want to do it.
Klaz