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!
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;
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
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;
Appreciate both of you for your help
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.