topic Combining Data Vertically in SAS Programming

Combining Data Vertically

jporterf — Thu, 10 Dec 2020 15:28:23 GMT

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!

Re: Combining Data Vertically

SASKiwi — Tue, 03 Nov 2020 02:28:32 GMT

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;

Re: Combining Data Vertically

hhinohar — Tue, 03 Nov 2020 05:24:17 GMT

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;

Re: Combining Data Vertically

jporterf — Tue, 03 Nov 2020 06:15:59 GMT

Wow that's very detailed, thank you!

Re: Combining Data Vertically

jporterf — Tue, 03 Nov 2020 06:16:45 GMT

Thank you! I tried this and it worked!

Re: Combining Data Vertically

hhinohar — Tue, 03 Nov 2020 06:26:16 GMT

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 .