BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Please see:

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.

View solution in original post

9 REPLIES 9
Reeza
Super User

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

sasphd
Lapis Lazuli | Level 10

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;

Reeza
Super User

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

Chwck your logic. 

sasphd
Lapis Lazuli | Level 10

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

I am sure that my program does not seem good. 

Can you please formulate your solution more clearly

Reeza
Super User

@sasphd wrote:

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

I am sure that my program does not seem good. 

Can you please formulate your solution more clearly


Does it work?

 

A SQL solution is the correct answer. Your sample data is misleading because you read everything in as character although its numeric. 

 


@sasphd wrote:

 

Can you please formulate your solution more clearly


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...

 

sasphd
Lapis Lazuli | Level 10

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.

 

ballardw
Super User

Please see:

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.

Reeza
Super User

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

sasphd
Lapis Lazuli | Level 10
I know, thanks for your help Reeza

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1546 views
  • 0 likes
  • 3 in conversation