Hello! I'm trying to combine two data sets into a single data set and I'm currently stuck. I imported both data sets into SAS as delimited raw data.
Here is data set #1:
| Var1 | Var2 | Var3 | Var4 | Var5 |
1 | - | - | - | - | - |
2 | - | - | - | - | - |
3 | - | - | - | - | - |
… | … | … | … | … | … |
And here is data set #2:
| Var1 | Var2 | Var3 | Var4 |
1 | - | - | - | - |
2 | - | - | - | - |
3 | - | - | - | - |
… | … | … | … | … |
My output data is only showing the values for the CM data set. I need to concatenate and rename variables in the other data set, and also rename a variable for the CM data set. Do I need to rename and concatenate variables within each individual data set first, or is there's a way to do both actions within the data step I shared above?
Thank you!
Try this - to append your datasets just use one SET statement then list your datasets:
DATA WORK.AllSites;
RETAIN Institution;
LENGTH SexCd $1.
Institution $11.
Name $15.;
SET WORK.StJohns (RENAME = ( Site = Institution
PatientID = SubjID
Sex = SexCd)
)
WORK.CM (RENAME = (Gender = SexCd)
);
Name = FirstNm||","||LastNm;
DROP FirstNm LastNm;
RUN;
Try this - to append your datasets just use one SET statement then list your datasets:
DATA WORK.AllSites;
RETAIN Institution;
LENGTH SexCd $1.
Institution $11.
Name $15.;
SET WORK.StJohns (RENAME = ( Site = Institution
PatientID = SubjID
Sex = SexCd)
)
WORK.CM (RENAME = (Gender = SexCd)
);
Name = FirstNm||","||LastNm;
DROP FirstNm LastNm;
RUN;
Thank you! I tried this and it worked!
data have1;
infile datalines dlm="09"x;
input Site:$11. PatientID FirstNm $ LastNm $ Sex $;
datalines;
St Johns 203 Daniel Taylor M
St Johns 206 Helen Davis F
St Johns 208 Betty Smith f
;
run;
data have2;
infile datalines dlm="09"x;
input Institution:$11. SubjID Name:$15. Gender $;
datalines;
City Medics 102 Wilson, Steven Male
City Medics 105 Moore, Chris male
City Medics 109 Jackson, Sharon Female
;
run;
*hash;
DATA _null_;
if _N_=1 then
do;
RETAIN Institution;
LENGTH Institution $11. SubjID 8 Name $15. sexcd $10. ;
declare hash h(multidata:"y");
h.definekey("Institution");
h.definedata("Institution","SubjID","name","sexcd");
h.definedone();
call missing(subjid);
end;
do until(last1);
SET WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) end=last1;
Name=FirstNm||","||LastNm;
h.add();
end;
do until(last2);
set have2(RENAME=(Gender=SexCd)) end=last2;
h.add();
end;
h.output(dataset:"want");
RUN;
*proc sql;
proc sql;
create table want as
select a.institution,
a.subjid,
cats(a.firstnm,",",a.lastnm) as name,
a.sexcd
from WORK.have1 (RENAME=(Site=Institution PatientID=SubjID sex=sexcd)) a
union
select b.institution,
b.subjid,
b.name,
b.gender as sexcd
from work.have2 b;
quit;
Wow that's very detailed, thank you!
My pleasure. If you could attach your program in a running man icon with detailed input and expected output, more detailed answer will be posted in the future .
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.