Merge two files and populate values in all cells

[abdulla]

set A				Set B					Result
ID	Yr	income		yr	pr1	pr2	pr3		ID	Yr	income	pr1	pr2	pr3
101	2011	2122		2011	0.54	0.25	0.36		101	2011	2122	0.54	0.25	0.36
101	2012	1000		2012	0.47	0.95	0.147		101	2012	1000	0.47	0.95	0.147
101	2013	2000		2013	0.213	0.254	0.369		101	2013	2000	0.213	0.254	0.369
102	2011	1450							102	2011	1450	0.54	0.25	0.36
102	2012	9000							102	2012	9000	0.47	0.95	0.147
102	2013	3250							102	2013	3250	0.213	0.254	0.369
103	2011	4500							103	2011	4500	0.54	0.25	0.36
103	2012	3254							103	2012	3254	0.47	0.95	0.147
103	2013	2987							103	2013	2987	0.213	0.254	0.369
104	2011	1250							104	2011	1250	0.54	0.25	0.36
104	2012	45069							104	2012	45069	0.47	0.95	0.147
104	2013	2587							104	2013	2587	0.213	0.254	0.369
105	2011	3698							105	2011	3698	0.54	0.25	0.36
105	2012	9875							105	2012	9875	0.47	0.95	0.147
105	2013	10000							105	2013	10000	0.213	0.254	0.369
106	2011	25893							106	2011	25893	0.54	0.25	0.36
106	2012	2005							106	2012	2005	0.47	0.95	0.147
106	2013	24793							106	2013	24793	0.213	.254	0.369

Hi, I  want the results as given in result section. After we merge, I want to populate the data from set B in my result. I have to merge data by yr. Please help

[Reeza]

What have you tried so far?
It seems like a straightforward merge to me.

Check the Match Merge section on this page for detailed examples including code:
https://documentation.sas.com/?docsetId=lrcon&docsetTarget=n1tgk0uanvisvon1r26lc036k0w7.htm&docsetVe...

[abdulla]

I checked there. But I don't see how it works for my example. My code was
Data want;
merge setA SetB;
By yr;
run;
It merges but doesn't populate numbers from set B in all the cells. Some cell remain empty

[PaigeMiller]

---

@abdulla wrote:
I checked there. But I don't see how it works for my example. My code was
Data want;
merge setA SetB;
By yr;
run;
It merges but doesn't populate numbers from set B in all the cells. Some cell remain empty

---

Did you sort setA and setB by year? (Actually setB doesn't need to be sorted, it already is)

Can you show us the incorrect data set that got created?

[abdulla]

I think in my file the date is not formatted the same way as in other. in one file date is Best12 and informat is 12 (this is the sas format) format and in the other file Date is formatted as YYMMDDN8. informat YYMMDD6. Could you please show me how I can format date as Best12 and infomat 12

[Reeza]

---

@abdulla wrote:
I think in my file the date is not formatted the same way as in other. in one file date is Best12 and informat is 12 (this is the sas format) format and in the other file Date is formatted as YYMMDDN8. informat YYMMDD6.

---

You've shown data with only years not dates of any kind. A formatted variable of YYMMDDN8 would not look like the example you posted:

ID	Yr	income		yr	pr1	pr2	pr3		ID	Yr	income	pr1	pr2	pr3
101	2011	2122		2011	0.54	0.25	0.36		101	2011	2122	0.54	0.25	0.36
101	2012	1000		2012	0.47	0.95	0.147		101	2012	1000	0.47	0.95	0.147
101	2013	2000		2013	0.213	0.254	0.369		101	2013	2000	0.213	0.254	0.369

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

To solve this issue you can convert each variable first to the same type, numeric as a year, a SAS date with a year format or a character variable with the year and then merge them. 

You could also do the merge via SQL and do the conversion in the JOIN step. 

[PaigeMiller]

Formatting should have no impact on merging. Show us the code and the log and your data.

[Tom · Accepted solution]

---

@abdulla wrote:

I think in my file the date is not formatted the same way as in other. in one file date is Best12 and informat is 12 (this is the sas format) format and in the other file Date is formatted as YYMMDDN8. informat YYMMDD6. Could you please show me how I can format date as Best12 and infomat 12

---

So one dataset has just numbers and the other as actual date values. 

You should convert the one where the dates were read in wrong into actual date values.  Then you can work with them as dates.

I recommend attaching the YYMMDD10. format instead of YYMMDDN8. format so that when you print the values humans will recognize them as dates.

data fixed_A ;
  set A;
  datevar= input(put(datevar,8.),yymmdd8.);
  format datevar yymmdd10.;
run;

[abdulla]

Thank you very much Tom.

[Reeza]

Post the code you ran and exact log please. 

---

@abdulla wrote:
I checked there. But I don't see how it works for my example. My code was
Data want;
merge setA SetB;
By yr;
run;
It merges but doesn't populate numbers from set B in all the cells. Some cell remain empty

---

[Tom]

Seems straight forward.  Note make sure that the "extra" variables in B, PR1-PR3, do not already exist in A.

data a ;
  input ID Yr income  ;
cards;
101 2011 2122  
101 2012 1000  
101 2013 2000  
102 2011 1450  
102 2012 9000  
102 2013 3250  
103 2011 4500  
;

data b;
  input yr pr1 pr2 pr3 ;
cards;
2011 0.54 0.25 0.36
2012 0.47 0.95 0.147
2013 0.213 0.254 0.369
;

proc sort data=a;
  by yr id ;
run;

data want;
  merge a b ;
  by yr;
run;

proc print;
run;

Obs     ID     Yr     income     pr1      pr2      pr3

 1     101    2011     2122     0.540    0.250    0.360
 2     102    2011     1450     0.540    0.250    0.360
 3     103    2011     4500     0.540    0.250    0.360
 4     101    2012     1000     0.470    0.950    0.147
 5     102    2012     9000     0.470    0.950    0.147
 6     101    2013     2000     0.213    0.254    0.369
 7     102    2013     3250     0.213    0.254    0.369