how to get the third table based on the two?

[zhangda]

Hello,

I have two tables year2015, year2016, but how can I get the third table ds1 based on the two by coding sas? Thanks!

data year2015;
input year $ resp ;
datalines;
null 0.35
s_300 0.55
s_640 0.65
s_670 0.75
s_700 0.47
s_730 0.58
s_760 0.69
;run;
data year2016;
input year $ resp ;
datalines;
null 0.45
s_300 0.65
s_640 0.42
s_670 0.35
s_700 0.83
s_730 0.48
s_760 0.69
;run;

data ds1;
input year $ mid $ resp exp rev;
datalines;
null d_2016 0.45 0.45 0.35
null d_2015 0.35 0.45 0.35
s_300 d_2016 0.65 0.65 0.55
s_300 d_2015 0.55 0.65 0.55
s_640 d_2016 0.42 0.42 0.65
s_640 d_2015 0.65 0.42 0.65
s_670 d_2016 0.35 0.35 0.75
s_670 d_2015 0.75 0.35 0.75
s_700 d_2016 0.83 0.83 0.47
s_700 d_2015 0.47 0.83 0.47
s_730 d_2016 0.48 0.48 0.58
s_730 d_2015 0.58 0.48 0.58
s_760 d_2016 0.67 0.67 0.69
s_760 d_2015 0.69 0.67 0.69
;
run;

[Reeza]

Ok...what's the relationship between the tables...

[Astounding]

That depends.  Where do EXP and REV come from if they're not part of the incoming data sets?

[zhangda]

there is no EXP and REV, I have to hard code it.

[ballardw]

And another way to get something similar. Mid has the data set names here but could be modified with string functions to get D_XXXX if that format is explicitly required.

data want;
   set year2015 year2016 indsname=name;
   mid=name;
run;

proc sort data=want; 
   by year mid;
run;

[Astounding · Accepted solution]

OK, let's ignore REV and EXP since you will be hard-coding them.  Sort the data sets first, if they are not already sorted:

proc sort data=year2015;

by year;

run;

proc sort data=year2016;

by year;

run;

Then combine them:

data want;

set year2016 (in=from2016) year2015 (in=from2015);

by year;

if from2016 then mid='d_2016';

else mid='d_2015';

run;