topic join two table using SQL in SAS Procedures

join two table using SQL

sasphd — Wed, 11 Jan 2017 15:02:34 GMT

I have tables 1 and 2 and I want table 3

Re: join two table using SQL

LinusH — Wed, 11 Jan 2017 15:11:37 GMT

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

Also, what have you tried so far?

Re: join two table using SQL

ballardw — Wed, 11 Jan 2017 15:22:14 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: join two table using SQL

sasphd — Wed, 11 Jan 2017 16:05:39 GMT

i did not succeed to attach sas files why

Re: join two table using SQL

sasphd — Wed, 11 Jan 2017 16:37:38 GMT

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

Re: join two table using SQL

LinusH — Fri, 13 Jan 2017 07:05:37 GMT

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.