DATA Step, Macro, Functions and more

Joining tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

Idfirst_termProgram
610199601Libarts
610201001Medtran
610201102Photo

 

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

IdTermCRN
610199602211
610199702213
610200501102
610200903302
610200903321
610201001124
610201001153
610201001150
610201001142
610201002225
610201002242
610201002244
610201003316
610201101103
610201101121
610201101142
610201101145
610201102242
610201102242
610201103315
610201201140
610201201140
610201201150
610201202211
610201202241
610201202242

 

Basically, the joined table should look like below. 

 

IdTermCRNProgram
610199602211Libarts
610199702213Libarts
610200501102Libarts
610200903302Libarts
610200903321Libarts
610201001124Medtran
610201001153Medtran
610201001150Medtran
610201001142Medtran
610201002225Medtran
610201002242Medtran
610201002244Medtran
610201003316Medtran
610201101103Medtran
610201101121Medtran
610201101142Medtran
610201101145Medtran
610201102242Photo
610201102242Photo
610201103315Photo
610201201140Photo
610201201140Photo
610201201150Photo
610201202211Photo
610201202241Photo
610201202242Photo

 

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 Smiley Happy

 

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;

View solution in original post


All Replies
PROC Star
Posts: 394

Re: Joining tables

This might be one way to solve it Smiley Happy

 

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

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 Smiley Happy

 

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 Smiley Happy

//Fredrik

Occasional Contributor
Posts: 14

Re: Joining tables

Thank you! Makes sense!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 207 views
  • 0 likes
  • 2 in conversation