one to many

[jcis7]

Hello, any help you can give is much appreciated!

I have a two files.

One with 10,000 unique facility codes listed once, along with their names, address, etc, file A

The other file, has the facility codes (which may or may not be listed in file A) listed more than once; this is file B

I want to add the information from file B to file A

File A

Facility Code    Name

00000001         Shining Star

00000002         Peter Pan

09201929         Leap Frog

File B

Facility Code    enroll    ethnic

00000001         12         Asian

00000001         19         Black

00000001         22         Hispanic

00000002         22        Asian

00000002         3         Black

00000002         2         Hispanic

09382909         3          White

Final Desired File

FacilityCode   Name            Asian Black Hispanic White

00000001       Shining Star    12     19      22         

00000002        Peter Pan       22       3        2         

09201929         Leap Frog

is it better to use a data step and create four new fields:

If ethnic='Asian' then Asian=enroll;

if ethnic='Black' then Black=enroll;

etc?

I'm not quite getting how you can get the final table though...Thanks!

[Reeza]

It's not a one step process.

I would recommend:

1. Transpose dataset B to get the required structure (proc transpose with ID=ethnic var=enroll)

2. Merge B and A

[ballardw · Accepted solution]

Possibly:

where A and B refer to a SAS data set made from files A and B.

To get the columns you'll need. NOTE: if the Ethnic variable has different spellings (typos) then you may get more variables than you think.

proc transpose data=b out=btrans(drop=_name_)                                                                                

;                                                                                                                                      

id ethnic;                                                                                                                             

Var enroll;                                                                                                                            

by FacilityCode;                                                                                                                       

run;

proc sql;

     create table want as

     select a.name, btrans.*

     from A left join btrans

          on a.FacilityCode=Btrans.FacilityCode;

quit;

[jcis7]

Appreciate both of you for your help