I would like to make the third table using the first two tables.
I thought of the following syntax:
proc sql; create table t1 as select *
from t3 a left join t2 b
on a.year=b.year and a.id=b.id;quit;
However, it only showed the first observation each year from the table 1.
Please help me to figure out.
pics or screen shots doesn't help. Please post the values as text
See my footnotes for how to post example data and code.
@withpeople86 wrote:
I would like to make the third table using the first two tables.
I thought of the following syntax:
proc sql; create table t1 as select *
from t3 a left join t2 b
on a.year=b.year and a.id=b.id;quit;
However, it only showed the first observation each year from the table 1.
Please help me to figure out.
Which table is t3? which is t2?
What formats are currently involved with the variables? When you compare or join with = the values have to actually be equal. Numeric variables with decimals portions suppressed by "look" the same when examining tables but not be equal. If the variables are text you run into potential issues with leading characters.
did you try
proc sql; create table t1 as select a.*,b.var2,b.var3 from t3 a left join t2 b on a.year=b.year and a.id=b.id; quit;
Your table T! has one record with YEAR=3, ID=4. And T2 has two such records, so T3 should also have two such records, but you posting shows only one. If that is an error, than this program takes advantage of the fact that both T1 and T2 are sorted by ID/YEAR:
data t1;
input year id var1 :$1.;
datalines;
1 1 a
2 1 b
3 1 a
4 1 b
1 2 b
2 2 b
3 2 a
4 2 b
1 3 b
2 3 b
3 3 b
4 3 c
1 4 c
2 4 a
3 4 b
4 4 a
run;
data t2;
input year id var2 var3;
datalines;
1 1 1 1
1 1 1 2
1 1 1 13
2 1 1 3
1 2 0 14
1 2 0 5
3 2 0 5
2 3 1 2
2 3 1 3
4 3 1 5
3 4 1 3
3 4 0 5
4 4 0 2
4 4 1 1
run;
data want;
merge t1 t2;
by id year;
run;
This program works because T1 has only 1 record for each YEAR/ID combination.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.