Introduction to joins in SAS using PROC SQL

[@scottau743]

Hello I would like to join two table but one value with hyphen and one without hyphen. Can you advise how should I code it so that the hyphen will be disregarded? Thanks in advance for help
Table studentAge
Student_id Age
Abc-123-dd 19
Ccd-578-ee 22
Table studentGender
Student_no Gender
abc123dd F
ccd578ee M
```
Proc sql;
Select a.*, b.*
From studentAge as a
Left join studentGender as b
On a.Student_id = b.Student_no
;
Quit;
```

[@patlongcodes]

I think you'd be best off making use of the `compress()` function to eliminate the '-' from the join and also the `lowcase()` function because of the upper case letter in the student_id column. The fully reporducible snippet below should solve your problem
data studentAge;
input student_id $10. age;
datalines;
Abc-123-dd 19
Ccd-578-ee 22
;
run;

data studentGender;
input Student_no $8. Gender $2.;
datalines;
abc123dd F
ccd578ee M
;
run;


proc sql;
create table combined as
select a.*, b.*
from studentAge as a
left join studentGender as b
on lowcase(compress(a.Student_id,'-')) = b.Student_no;
quit;

[@scottau743]

Thank you so much

[@scottau743]

Actually I have one more question regarding date format and want to seek for your advice
I need to add a where clause so that only effective_date or process_date greater than 20230123 will be shown. However, in the existinf table effective_date is in numeric format and effective_date is in text format in the existing table and the table size is big so I would not be able to create a separate table to clean up the format.
Proc sql;
Select *
From OperationTime
Where process_date > 20230123
or
Datepart(effective_date) >= ’23Jan2023’d
Table OperationTime
Tasks Effective_date Process_date (text)
C234 20230101 2013-01-24 16:58:54.3028
B355 20201006 2020-01-16 16:58:54.3028
D777 20221005 2010-01-24 16:58:54.3028

[@patlongcodes]

​@@scottau743 switching this line in for the second part of your where clause might solve it. Using The `mdy()` funtion you'll have a SAS date to compare to a SAS date if that makes sense. mdy(input(substr(effective_date,5,2),2.),input(substr(effective_date,5,2),2.),input(substr(effective_date,1,4)),4.) >= ’23Jan2023’d