## Joining tables

Solved
Occasional Contributor
Posts: 14

# Joining tables

Hello everyone

I have two tables. I need to join them and connect the columns. Below are the two source tables and the third table is the desired table.

In table 1 the first_term is the term that student began that program.

Table 1

 Id first_term Program 610 199601 Libarts 610 201001 Medtran 610 201102 Photo

In table 2, the "term" column has all the terms the student has registered for a class. if the starting term in the "term" column is greater than or equal to the "first_term" in table 1, then it means that class (CRN number) is tied to that program. For example, the first program the student began is LIBARTS in 199601 (table 1). In table 2, we can see after 199601, this student took courses till the term reaches to the beginning of the new program which is 201001. This means the first 5 observations belong to the LIBARTS program.

Table 2

 Id Term CRN 610 199602 211 610 199702 213 610 200501 102 610 200903 302 610 200903 321 610 201001 124 610 201001 153 610 201001 150 610 201001 142 610 201002 225 610 201002 242 610 201002 244 610 201003 316 610 201101 103 610 201101 121 610 201101 142 610 201101 145 610 201102 242 610 201102 242 610 201103 315 610 201201 140 610 201201 140 610 201201 150 610 201202 211 610 201202 241 610 201202 242

Basically, the joined table should look like below.

 Id Term CRN Program 610 199602 211 Libarts 610 199702 213 Libarts 610 200501 102 Libarts 610 200903 302 Libarts 610 200903 321 Libarts 610 201001 124 Medtran 610 201001 153 Medtran 610 201001 150 Medtran 610 201001 142 Medtran 610 201002 225 Medtran 610 201002 242 Medtran 610 201002 244 Medtran 610 201003 316 Medtran 610 201101 103 Medtran 610 201101 121 Medtran 610 201101 142 Medtran 610 201101 145 Medtran 610 201102 242 Photo 610 201102 242 Photo 610 201103 315 Photo 610 201201 140 Photo 610 201201 140 Photo 610 201201 150 Photo 610 201202 211 Photo 610 201202 241 Photo 610 201202 242 Photo

Any help will be greatly appreciated. Thanks.

Accepted Solutions
Solution
‎09-06-2017 09:46 AM
PROC Star
Posts: 394

## Re: Joining tables

Hmm, I think it works when I run it

Does your code look like this:

data one;

length id 8 first_term \$6 program\$32;

input id first_term program;

datalines;

610 199601 Libarts

610 201001 Medtran

610 201102 Photo

510 199701 Libarts1

510 201101 Medtran1

510 201302 Photo1

;

run;

data two;

length Id 8 Term \$6 CRN \$32;

input id term crn;

datalines;

610 199602 211

610 199702 213

610 200501 102

610 200903 302

610 200903 321

610 201001 124

610 201001 153

610 201001 150

610 201001 142

610 201002 225

610 201002 242

610 201002 244

610 201003 316

610 201101 103

610 201101 121

610 201101 142

610 201101 145

610 201102 242

610 201102 242

610 201103 315

610 201201 140

610 201201 140

610 201201 150

610 201202 211

610 201202 241

610 201202 242

510 199702 211

510 199802 213

510 200601 102

510 200904 302

510 200905 321

510 201101 124

510 201101 153

510 201101 150

510 201101 142

510 201102 225

510 201102 242

510 201102 244

510 201103 316

510 201401 103

510 201401 121

;

run;

proc sort data=one;

by id descending first_term;

run;

data one;

set one;

by id descending first_term;

length fromdate todate \$6;

fromdate = first_term;

todate = lag(first_term);

if first.id then

todate = '599901';

run;

proc sql;

create table tog as

select two.id, two.term, two.crn, one.program from one

inner join two

on one.id = two.id

and one.fromdate <= two.term < one.todate

;

run;

All Replies
PROC Star
Posts: 394

## Re: Joining tables

This might be one way to solve it

data one;

length id 8 first_term \$6 program\$32;

input id first_term program;

datalines;

610 199601 Libarts

610 201001 Medtran

610 201102 Photo

;

run;

data two;

length Id 8 Term \$6 CRN \$32;

input id term crn;

datalines;

610 199602 211

610 199702 213

610 200501 102

610 200903 302

610 200903 321

610 201001 124

610 201001 153

610 201001 150

610 201001 142

610 201002 225

610 201002 242

610 201002 244

610 201003 316

610 201101 103

610 201101 121

610 201101 142

610 201101 145

610 201102 242

610 201102 242

610 201103 315

610 201201 140

610 201201 140

610 201201 150

610 201202 211

610 201202 241

610 201202 242

;

run;

proc sort data=one;

by id descending first_term;

run;

data one;

set one;

length fromdate todate \$6;

fromdate = first_term;

todate = lag(first_term);

if todate = '' then todate = '599901';

run;

proc sql;

create table tog as

select two.id, two.term, two.crn, one.program from one

inner join two

on one.id = two.id

and one.fromdate <= two.term < one.todate

;

run;

Occasional Contributor
Posts: 14

## Re: Joining tables

Thank you for your response. This code works if there is only one student. But I have many students. The 'todate' has to be sorted by id? Currently the 'lag' function picks the date from previous observation which means it overlaps to another student? Hope i am making sense?

PROC Star
Posts: 394

## Re: Joining tables

Yes of, course, quick and wrong....

This is untested, but try:

data one;

set one;

by id descending first_term;

length fromdate todate \$6;

fromdate = first_term;

todate = lag(first_term);

if first.id then todate = '599901';

run;

Occasional Contributor
Posts: 14

## Re: Joining tables

Thanks Fredrik. Tried that, did not work as it still picks the terms from previous ids.

Solution
‎09-06-2017 09:46 AM
PROC Star
Posts: 394

## Re: Joining tables

Hmm, I think it works when I run it

Does your code look like this:

data one;

length id 8 first_term \$6 program\$32;

input id first_term program;

datalines;

610 199601 Libarts

610 201001 Medtran

610 201102 Photo

510 199701 Libarts1

510 201101 Medtran1

510 201302 Photo1

;

run;

data two;

length Id 8 Term \$6 CRN \$32;

input id term crn;

datalines;

610 199602 211

610 199702 213

610 200501 102

610 200903 302

610 200903 321

610 201001 124

610 201001 153

610 201001 150

610 201001 142

610 201002 225

610 201002 242

610 201002 244

610 201003 316

610 201101 103

610 201101 121

610 201101 142

610 201101 145

610 201102 242

610 201102 242

610 201103 315

610 201201 140

610 201201 140

610 201201 150

610 201202 211

610 201202 241

610 201202 242

510 199702 211

510 199802 213

510 200601 102

510 200904 302

510 200905 321

510 201101 124

510 201101 153

510 201101 150

510 201101 142

510 201102 225

510 201102 242

510 201102 244

510 201103 316

510 201401 103

510 201401 121

;

run;

proc sort data=one;

by id descending first_term;

run;

data one;

set one;

by id descending first_term;

length fromdate todate \$6;

fromdate = first_term;

todate = lag(first_term);

if first.id then

todate = '599901';

run;

proc sql;

create table tog as

select two.id, two.term, two.crn, one.program from one

inner join two

on one.id = two.id

and one.fromdate <= two.term < one.todate

;

run;

Occasional Contributor
Posts: 14

## Re: Joining tables

Thanks Fredrik. This code worked. I swear I did the same way yesterday and it did not work. I am guessing I may have something wrong.

I created my own code but seems more complicated. Below is my code;

data sgbstdn1a (drop=x);
set sgbstdn;
by sgbstdn_pidm;
length fromdate todate \$6;
fromdate = first_term;
x=lag(first_term);
if first.sgbstdn_pidm then do;
todate='999999';
end; else do;
todate=x;
end;
run;

PROC Star
Posts: 394

## Re: Joining tables

The important thing is to sort in descending date order since first.term actually is the last one, and you need to go in reverse order to get the date intervals correct

//Fredrik

Occasional Contributor
Posts: 14

## Re: Joining tables

Thank you! Makes sense!

☑ This topic is solved.