topic Re: Merge two files and populate values in all cells in SAS Programming

Merge two files and populate values in all cells

abdulla — Tue, 08 Oct 2019 18:38:13 GMT

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

Re: Merge two files and populate values in all cells

Reeza — Tue, 08 Oct 2019 18:42:35 GMT

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&docsetVersion=9.4&locale=en#n0vt2rcdm9sc0zn1cmagtz09c1lw

Re: Merge two files and populate values in all cells

abdulla — Tue, 08 Oct 2019 19:18:49 GMT

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

Re: Merge two files and populate values in all cells

PaigeMiller — Tue, 08 Oct 2019 19:23:01 GMT

@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?

Re: Merge two files and populate values in all cells

abdulla — Tue, 08 Oct 2019 19:29:33 GMT

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

Re: Merge two files and populate values in all cells

Reeza — Tue, 08 Oct 2019 19:28:47 GMT

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

Re: Merge two files and populate values in all cells

Reeza — Tue, 08 Oct 2019 19:32:13 GMT

@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-data-AKA-generate/ta-p/258712

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.

Re: Merge two files and populate values in all cells

PaigeMiller — Tue, 08 Oct 2019 19:41:02 GMT

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

Re: Merge two files and populate values in all cells

Tom — Tue, 08 Oct 2019 19:51:49 GMT

@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;

Re: Merge two files and populate values in all cells

Tom — Tue, 08 Oct 2019 19:57:13 GMT

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

Re: Merge two files and populate values in all cells

abdulla — Tue, 08 Oct 2019 20:05:45 GMT

Thank you very much Tom.