Help using Base SAS procedures

join two table using SQL

Reply
Frequent Contributor
Posts: 118

join two table using SQL

I have tables 1 and 2 and I want table 3

Super User
Posts: 5,441

Re: join two table using SQL

Don't attach Excel files. Include any sample data as data step w/ datalines/cards.

Also, what have you tried so far?

Data never sleeps
Super User
Posts: 11,343

Re: join two table using SQL

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

If you have your example data in SAS data sets then the instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to create data step code that you can post in the forum, use the code box usinging the {i} menu item, or attach as text files. Then we know exactly what the data looks like.

Frequent Contributor
Posts: 118

Re: join two table using SQL

i did not succeed to attach sas files why

Frequent Contributor
Posts: 118

Re: join two table using SQL

table 1

 

Rank_NO crsp_fundno MGR_DT
1                      53           1991-01-01
1                      53            1999-01-01
2                    105             1985-01-01
2                    105             1985-06-01

 

 

table 2

Rank_NO crsp_fundno caldt mret
1 53 1990-11-30 1,0459
1 53 1990-12-31 1,0250
1 53 1991-01-31 1,0435
1 53 1991-02-28 1,0499
1 53 1991-03-28 1,0078
1 53 1999-01-29 0,9810
1 53 1999-02-26 1,0002
1 53 1999-03-31 1,0238
2 105 1984-12-31 1,0238
2 105 1985-01-31 1,0238
2 105 1985-02-28 1,0238
2 105 1985-03-29 1,0238
2 105 1985-04-30 1,0238
2 105 1985-05-31 1,0238
2 105 1985-06-28 1,0238
2 105 1985-07-31 0,9990

 

 

table 3 

Rank_NO crsp_fundno caldt mret MGR_DT
1 53 1990-11-30 1,0459  
1 53 1990-12-31 1,0250  
1 53 1991-01-31 1,0435 1991-01-01
1 53 1991-02-28 1,0499 1991-01-01
1 53 1991-03-28 1,0078 1991-01-01
1 53 1999-01-29 0,9810 1999-01-01
1 53 1999-02-26 1,0002 1999-01-01
1 53 1999-03-31 1,0238 1999-01-01
2 105 1984-12-31 1,0238  
2 105 1985-01-31 1,0238 1985-01-01
2 105 1985-02-28 1,0238 1985-01-01
2 105 1985-03-29 1,0238 1985-01-01
2 105 1985-04-30 1,0238 1985-01-01
2 105 1985-05-31 1,0238 1985-01-01
2 105 1985-06-28 1,0238 1985-06-01
2 105 1985-07-31 0,999 1985-06-01
Super User
Posts: 5,441

Re: join two table using SQL

It would be easier to join if you got an end date for mgr. If the real data set is larger and unpractical for manual edit use a data step with BY and RETAIN to create those.
Then you could do the join using caldt between mgr_dt and mgr_enddt.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 206 views
  • 0 likes
  • 3 in conversation