join two tables with dummy

Solved
Frequent Contributor
Posts: 126

join two tables with dummy

I have table A and B to join to obtain table C

data A;
input year\$ yr2007\$ yr2008\$ yr2009\$;
cards;
2007 1 0 0
2008 0 1 0
2009 0 0 1
run;
data B;
input rank_no\$ rankdate\$ ;
cards;
1 20071231
1 20081231
1 20091231
2 20071231
2 20081231
2 20091231
run;

data C;
input rank_no\$ rankdate\$ yr2007\$ yr2008\$ yr2009\$ ;
cards;
1 20071231 1 0 0
1 20081231 0 1 0
1 20091231 0 0 1
2 20071231 1 0 0
2 20081231 0 1 0
2 20091231 0 0 1
run;

Accepted Solutions
Solution
‎04-06-2017 12:53 PM
Super User
Posts: 13,546

Re: join two tables with dummy

data A;
input year yr2007 yr2008 yr2009;
cards;
2007 1 0 0
2008 0 1 0
2009 0 0 1
run;
data B;
informat rankdate yymmdd.;
format rankdate yymmdd10.;
input rank_no rankdate ;
cards;
1 20071231
1 20081231
1 20091231
2 20071231
2 20081231
2 20091231
run;

proc sql;
create table JOINT_fixed as
select Distinct A.*, B.*
from B
Left join A
on Year(B.rankdate)= A.year ;
quit;

To use the year function as in Year(b.rankdate) the variable b.rankdate must be a SAS datevalue to make sense. I modified the example code to use numeric values as that makes more sense then reading everything as character values (which is what those \$ do) and used a specific informat to read the RANKDATE variable as a date value.

Also since the variable A.year is already a year value then the function Year(a.year) is not needed, just compare the actual value.

If your existing data for the variables is actually all character you may want to go back a step or two  in your process and change things to get numeric values.

All Replies
Super User
Posts: 23,726

Re: join two tables with dummy

Create a year variable in data set B and merge on that value.

Frequent Contributor
Posts: 126

Re: join two tables with dummy

I do this and it did not work can you help me please!!!!!!!!!!!!!!!!!!

proc sql;
create table JOINT_fixed as
select Distinct A.*, B.*
from B
Left join A
on Year(B.rankdate)=Year(A.year)
quit;

Super User
Posts: 23,726

Re: join two tables with dummy

sasphd wrote:

I do this and it did not work can you help me please!!!!!!!!!!!!!!!!!!

proc sql;
create table JOINT_fixed as
select Distinct A.*, B.*
from B
Left join A
on Year(B.rankdate)=Year(A.year)
quit;

What happens when you do year(year)??????How can you take the year of a year?????Why a left join?????

Frequent Contributor
Posts: 126

Re: join two tables with dummy

Sorry it is a mistake I put Year(B.rankdate)=A.year

I am sure that my program does not seem good.

Super User
Posts: 23,726

Re: join two tables with dummy

sasphd wrote:

Sorry it is a mistake I put Year(B.rankdate)=A.year

I am sure that my program does not seem good.

Does it work?

sasphd wrote:

A join/merge is one of the very basics of working with data, week0, it's worth spending some time to understand it and learn it.

There's an entire 'chapter' on the topic in the documentation/user guide.

https://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p15jvywi5avt3cn1bee...

Frequent Contributor
Posts: 126

Re: join two tables with dummy

All my problem is that I never suceed to put data correctly in your forum. You have tried to help me to do it but I did not suceed.

Solution
‎04-06-2017 12:53 PM
Super User
Posts: 13,546

Re: join two tables with dummy

data A;
input year yr2007 yr2008 yr2009;
cards;
2007 1 0 0
2008 0 1 0
2009 0 0 1
run;
data B;
informat rankdate yymmdd.;
format rankdate yymmdd10.;
input rank_no rankdate ;
cards;
1 20071231
1 20081231
1 20091231
2 20071231
2 20081231
2 20091231
run;

proc sql;
create table JOINT_fixed as
select Distinct A.*, B.*
from B
Left join A
on Year(B.rankdate)= A.year ;
quit;

To use the year function as in Year(b.rankdate) the variable b.rankdate must be a SAS datevalue to make sense. I modified the example code to use numeric values as that makes more sense then reading everything as character values (which is what those \$ do) and used a specific informat to read the RANKDATE variable as a date value.

Also since the variable A.year is already a year value then the function Year(a.year) is not needed, just compare the actual value.

If your existing data for the variables is actually all character you may want to go back a step or two  in your process and change things to get numeric values.

Super User
Posts: 23,726

Re: join two tables with dummy

sasphd wrote:

All my problem is that I never suceed to put data correctly in your forum. You have tried to help me to do it but I did not suceed.

This is not 'my forum'.  I have no affiliation with SAS.

Frequent Contributor
Posts: 126

Re: join two tables with dummy

I know, thanks for your help Reeza
☑ This topic is solved.