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
@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 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?
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
@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.
Formatting should have no impact on merging. Show us the code and the log and your data.
@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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: