topic Re: one to many in SAS Programming

one to many

jcis7 — Thu, 25 Jun 2015 15:38:24 GMT

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!

Re: one to many

Reeza — Thu, 25 Jun 2015 16:05:18 GMT

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

Re: one to many

ballardw — Thu, 25 Jun 2015 16:07:01 GMT

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;

Re: one to many

jcis7 — Thu, 25 Jun 2015 19:56:56 GMT

Appreciate both of you for your help