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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.