BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

Hi All,

I am trying to retrieve the missing variable of Table2 from Table1 (within by group). Can someone help with the code please.

 

I have the following two datasets:

 

Table1: 

Student_ID     Course

101                 English

101                 Biology

102                Chemistry

103                English

103                Physics

103                Accounting

104                Biology

104                Chemistry  

 

Table2:

Student_ID  Course

101              English

102             Chemistry

103             English

103             Accounting

104             Chemistry  

 

 

Expected Output:

Student_ID     Course

101                 Biology

103                Physics

104                Biology

 

2 REPLIES 2
Pamela_JSRCC
Quartz | Level 8

proc sql;
select a.*
from table1 A
left join table2 B on b.student_id = a.student_id and
b.course = a.course
where missing (b.student_id);

ballardw
Super User

proc sql;

    create table want as

    select * from table1

    except

    select * from table2

    ;

quit;

 

If there are other variables needed you need to do some merging bak from the result to get them and have to replace the * with Student_Id, Course

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 854 views
  • 2 likes
  • 3 in conversation