BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
Reeza
Super User

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
Super User

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
Pyrite | Level 9

Appreciate both of you for your help Smiley Happy

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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