DATA Step, Macro, Functions and more

one to many

Accepted Solution Solved
Reply
Regular Contributor
Posts: 192
Accepted Solution

one to many

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!


Accepted Solutions
Solution
‎06-25-2015 12:07 PM
Super User
Posts: 10,514

Re: one to many

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;

View solution in original post


All Replies
Super User
Posts: 17,852

Re: one to many

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

Solution
‎06-25-2015 12:07 PM
Super User
Posts: 10,514

Re: one to many

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;

Regular Contributor
Posts: 192

Re: one to many

Appreciate both of you for your help Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 282 views
  • 3 likes
  • 3 in conversation