topic Re: Combining three datasets in SAS Programming

Combining three datasets

luvscandy27 — Thu, 16 Jan 2020 20:57:04 GMT

I have the following three datasets which all need to be combined together.

Data 1
Filedate ID Status monyear birdtype
1/25/2018 100 y JAN2018 Sparrow
2/28/2018 200 y FEB2018 robin
3/15/2018 300 n MAR2018 bluebird

Data 2
birdtype monyear Population
Sparrow JAN2018 100
robin FEB2018 150
bluebird MAR2018 150

Data 3
reportorder monyear startdt endddt extdt
1 JAN2018 1/31/2016 1/31/2017 4/30/2018
2 FEB2018 2/29/2016 2/28/2017 5/31/2018
3 MAR2018 4/30/2016 4/30/2017 7/31/2018

The datasets data1 and data 2 I merge together by birdtype and monyear using the following code:

data one_two_combined;
merge data1 data2;
by birdtype monyear;
run;

then I combine dataset one_two_combined with data 3 using the following code:

data all_combined;
merge one_two_combined data3;
by monyear;
run;

the codes above work but I would like to combine all the datasets in one step. Dataset one and two must be combined
by the birdtype and monyear and dataset three does not have a birdtype column so when I tried using the code below it
does not work.

data want;
merge data1 data2 data3;
by birdtype monyear;
run;

Any suggestions on how I can merge all three datasets in one step?

Re: Combining three datasets

PaigeMiller — Thu, 16 Jan 2020 20:59:52 GMT

so when I tried using the code below it does not work.

This does not convey any information that is useful in solving the problem. What did not work? Did you get an error in the LOG? Did you get the wrong result. Explain in detail.

Re: Combining three datasets

luvscandy27 — Thu, 16 Jan 2020 21:07:23 GMT

SAS will not allow me to merge all three datasets at one time. I get the error that dataset 3 does not have a birdtype variable.

Re: Combining three datasets

r_behata — Thu, 16 Jan 2020 21:28:13 GMT

One Way :

Data Data1;
input Filedate anydtdte. ID Status $ monyear $ birdtype $;
cards;
1/25/2018 100 y JAN2018 Sparrow
2/28/2018 200 y FEB2018 robin
3/15/2018 300 n MAR2018 bluebird
;
run;

Data Data2;
input birdtype $ monyear $ Population;
cards;
Sparrow JAN2018 100
robin FEB2018 150
bluebird MAR2018 150
run;
 
data Data3;
input reportorder  monyear $ (startdt endddt extdt) (anydtdte.);
cards;
1 JAN2018 1/31/2016 01/31/2017 04/30/2018
2 FEB2018 2/29/2016 02/28/2017 05/31/2018
3 MAR2018 4/30/2016 04/30/2017 07/31/2018
;
run;


data want;
length birdtype $15. Population reportorder startdt endddt extdt 8.;
	if _n_=1 then do;
		declare hash h1(dataset:"Data2");
		h1.definekey('birdtype','monyear');
		h1.definedata(all:'y');
		h1.definedone();

		declare hash h2(dataset:"Data3");
		h2.definekey('monyear');
		h2.definedata(all:'y');
		h2.definedone();
		call missing(birdtype,Population,reportorder,startdt,endddt,extdt);
	end;
	set Data1;

	rc1=h1.find();
	rc2=h2.find();

	drop rc1 rc2;
run;

Re: Combining three datasets

luvscandy27 — Thu, 16 Jan 2020 21:40:47 GMT

Thank you. I am very new to the hash object if at all possible could you please explain the code you have provided?

Re: Combining three datasets

luvscandy27 — Thu, 16 Jan 2020 22:27:58 GMT

I ran the code and did not receive any errors in the log but the values were extdt are all missing.