topic Re: Merging two files in SAS Programming

Merging two files

abdulla — Mon, 11 Feb 2019 22:53:01 GMT

File A
permno	date	rtrn
111	201202	0.00023696170
111	201203	0.025770281
111	201204	0.00817657
222	201302	0.020201225
222	201303	0.004788088
222	201304	0.041050945
333	201202	0.016503398
333	201203	0.037175513
333	201204	0.025401699
444	201302	0.032370619
444	201303	0.037818116
444	201304	0.011846338
555	201203	0.011245678

File B
date	rtrn	HML	SMB
201202	0.01	0.02	1.2
201203	0.014	1.4	3.5
201204	0.008	2.1	0.08
201302	0.023	3.4	1.69
201303	-0.25	0.006	2.14
201304	0.14	1.21	3.54

The result I want
permno	date	rtrn	rtrn	HML	SMB
111	201202	0.00023696170	0.01	0.02	1.2
111	201203	0.025770281	0.014	1.4	3.5
111	201204	0.00817657	0.008	2.1	0.08
222	201302	0.020201225	0.023	3.4	1.69
222	201303	0.004788088	-0.25	0.006	2.14
222	201304	0.041050945	0.14	1.21	3.54
333	201202	0.016503398	0.01	0.02	1.2
333	201203	0.037175513	0.014	1.4	3.5
333	201204	0.025401699	0.008	2.1	0.08
444	201302	0.032370619	0.023	3.4	1.69
444	201303	0.037818116	-0.25	0.006	2.14
444	201304	0.011846338	0.14	1.21	3.54
555	201203	0.011245678	0.014	1.4	3.5

So, Basically I want to merge two files(A and B) together and want to fill all the cells of File B according to date. If I merge by date, I don't get the result. File B covers only first 6 rows of file A. Please help.

Re: Merging two files

novinosrin — Mon, 11 Feb 2019 23:10:27 GMT



data file1;
input (permno	date 	rtrn) (:$20.);
cards;
111	201202	0.00023696170
111	201203	0.025770281
111	201204	0.00817657
222	201302	0.020201225
222	201303	0.004788088
222	201304	0.041050945
333	201202	0.016503398
333	201203	0.037175513
333	201204	0.025401699
444	201302	0.032370619
444	201303	0.037818116
444	201304	0.011846338
555	201203	0.011245678
;

data file2;
input (date	rtrn	HML	SMB) ($);
cards;
201202	0.01	0.02	1.2
201203	0.014	1.4	3.5
201204	0.008	2.1	0.08
201302	0.023	3.4	1.69
201303	-0.25	0.006	2.14
201304	0.14	1.21	3.54
;

data want;
set file1;
if _n_=1 then do;
if 0 then set file2;
   dcl hash H (dataset:"file2") ;
   h.definekey  ("date") ;
   h.definedata (all:'y') ;
   h.definedone () ;
   end;
if h.find() ne 0 then call missing(rtrn,HML,	SMB);
run;

Re: Merging two files

Astounding — Mon, 11 Feb 2019 23:31:35 GMT

First, figure out what to name your variables so you don't have two variables with the same name. Once that is done, sorting and merging by date gives you the right result.

Re: Merging two files

Tom — Tue, 12 Feb 2019 01:05:49 GMT

Merging by DATE seems to work fine.

data want;
  merge a(rename=(rtrn=rtrnA)) b(rename=(rtrn=rtrnB)) ;
  by date;
run;
proc sort;
  by permno date;
run;

Obs    permno     date       rtrnA      rtrnB     HML      SMB

  1     111      201202    0.000237     0.010    0.020    1.20
  2     111      201203    0.025770     0.014    1.400    3.50
  3     111      201204    0.008177     0.008    2.100    0.08
  4     222      201302    0.020201     0.023    3.400    1.69
  5     222      201303    0.004788    -0.250    0.006    2.14
  6     222      201304    0.041051     0.140    1.210    3.54
  7     333      201202    0.016503     0.010    0.020    1.20
  8     333      201203    0.037176     0.014    1.400    3.50
  9     333      201204    0.025402     0.008    2.100    0.08
 10     444      201302    0.032371     0.023    3.400    1.69
 11     444      201303    0.037818    -0.250    0.006    2.14
 12     444      201304    0.011846     0.140    1.210    3.54
 13     555      201203    0.011246     0.014    1.400    3.50

Re: Merging two files

abdulla — Tue, 12 Feb 2019 02:05:56 GMT

It is not working. I don't know why?

Re: Merging two files

novinosrin — Tue, 12 Feb 2019 02:36:43 GMT

Well the max we/I can do is test using the samples you give us. I'm sure the code worked for your sample. So perhaps you need to be clear with your data and requirement

Re: Merging two files

ChrisNZ — Tue, 12 Feb 2019 03:14:45 GMT

I don't see what the issue is. Am I missing something?

data FILE1 ;
input (PERMNO DATE RTRN) (:$20.);
cards;
111 201202 0.00023696170
111 201203 0.025770281
111 201204 0.00817657
222 201302 0.020201225
222 201303 0.004788088
222 201304 0.041050945
333 201202 0.016503398
333 201203 0.037175513
333 201204 0.025401699
444 201302 0.032370619
444 201303 0.037818116
444 201304 0.011846338
555 201203 0.011245678
run;

data FILE2;
input (DATE RTRN HML SMB) ($);
cards;
201202 0.01 0.02 1.2
201203 0.014 1.4 3.5
201204 0.008 2.1 0.08
201302 0.023 3.4 1.69
201303 -0.25 0.006 2.14
201304 0.14 1.21 3.54
run;

proc sql;
  select *
  from FILE1(rename=(RTRN=RTRNA)) 
      ,FILE2(rename=(RTRN=RTRNB)) 
  where FILE1.DATE=FILE2.DATE
  order by PERMNO, DATE;
quit;

PERMNO	DATE	RTRNA	DATE	RTRNB	HML	SMB
111	201202	0.00023696170	201202	0.01	0.02	1.2
111	201203	0.025770281	201203	0.014	1.4	3.5
111	201204	0.00817657	201204	0.008	2.1	0.08
222	201302	0.020201225	201302	0.023	3.4	1.69
222	201303	0.004788088	201303	-0.25	0.006	2.14
222	201304	0.041050945	201304	0.14	1.21	3.54
333	201202	0.016503398	201202	0.01	0.02	1.2
333	201203	0.037175513	201203	0.014	1.4	3.5
333	201204	0.025401699	201204	0.008	2.1	0.08
444	201302	0.032370619	201302	0.023	3.4	1.69
444	201303	0.037818116	201303	-0.25	0.006	2.14
444	201304	0.011846338	201304	0.14	1.21	3.54
555	201203	0.011245678	201203	0.014	1.4	3.5

Re: Merging two files

VRKiwi — Tue, 12 Feb 2019 03:19:33 GMT

This worked for me:

proc sort data=A; 
  by DATE;
run; 
 
data WANT;
  set A B; 
  merge A(rename=(RTRN=RTRNA)) B(rename=(RTRN=RTRNB)); 
  by DATE;
  keep PERMNO DATE RTRN RTRN HML SMB; 
run; 

proc sort data=WANT;
  by PERMNO DATE;
run;