Combining Two Datasets

[marianhabesland]

I am trying to combine the two datasets below. I am also adding formats so that they can be combined. I want the final dataset (WORK.AllSites) to look like this:

	Institution	SubjID	Name	SexCd
1	City Medics	102	Wilson, Steven	M
2	City Medics	105	Moore, Chris	M
3	City Medics	109	Jackson, Sharon	F
4	St John’s	203	Taylor, Daniel	M
5	St John’s	206	Davis, Helen	F
6	St John’s	208	Smith, Betty	F

When I combine them together the code does not work and I only get the CM data imported. What can I do to fix this?

data WORK.CM;
infile datalines delimiter = ':';
input Institution :$11.
SubjID :3.
Name :$15.
Gender :$6.;
datalines;
City Medics:102:Wilson, Steven:Male
City Medics:105:Moore, Christ:male
City Medics:109:Jackson, Sharon:Female
;
data WORK.StJohns;
infile datalines delimited = ',';
input Site :$9.
PatientID :3.
FirstNm :$6.
LastNm :$6.
Sex :$1.;
datalines;
St John's,203,Daniel, Taylor,M
St John's,206,Helen, Davis,F
St John's,208,Betty, Smith,f
;

data WORK.AllSites;

set WORK.StJohns;
SexCd1=propcase(Sex);
rename Site = Institution
PatientID = SubjID
SexCd1 = SexCd;
Name = catx(',', LastNm, FirstNm);
run;

data WORK.AllSites;
set WORK.CM;
SexCd = propcase(substr(Gender, 1, 1));
run;

data WORK.AllSites;
set WORK.StJohns
WORK.CM;
keep Institution SubjID Name SexCd;
run;

proc sort data = WORK.AllSites;
by Institution SubjID;
run;

[Astounding · Accepted solution]

Ignoring any other possible issues, this section of code creates a problem:

data WORK.AllSites;

set WORK.StJohns;
SexCd1=propcase(Sex);
rename Site = Institution
PatientID = SubjID
SexCd1 = SexCd;
Name = catx(',', LastNm, FirstNm);
run;

data WORK.AllSites;
set WORK.CM;
SexCd = propcase(substr(Gender, 1, 1));
run;

This code replaces AllSites ... first with the data from StJohns and then replaces it with the data from CM.  Since neither data set (StJohns nor CM) is changed by this portion of code, the final DATA step tries to combine data sets with incompatible formats.  Change the data set names here:

data WORK.Final_StJohns;

set WORK.StJohns;
SexCd1=propcase(Sex);
rename Site = Institution
PatientID = SubjID
SexCd1 = SexCd;
Name = catx(',', LastNm, FirstNm);
run;

data WORK.Final_CM;
set WORK.CM;
SexCd = propcase(substr(Gender, 1, 1));
run;

Then the SET statement in the final DATA step can use:

set Final_StJohns Final_CM;

[marianhabesland]

That makes sense. This makes it look a lot better! Only had to make some more minor changes and the output looked perfect. Thanks!