In my where clause, i'm trying to limit the datetimein selected (after AND), to only those dates in the compdate range i specify right after WHERE (WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd).
Is my query written correctly.
This is my goal- pull average daily theoretical (pulled from slots table) for the comp date plus one month ****Only for Q1 (01/01-03/31).
PROC SQL; SELECT sum(t1.TheoWin)/30 FROM Work.Slots t1 JOIN TDCX.vCXComps t2 ON t1.PtnId=t2.ptnid WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd AND datepart(t1.DateTimeIn) BETWEEN datepart(t2.compDate) AND INTNX('month', datepart(t2.compDate), 1,'b'); QUIT;
It looks like you require an inner join as you need valid date ranges from your T2 table. I'd also suggest you look at the output data first to confirm you are getting the right data like so:
PROC SQL;
create table test as
SELECT *
FROM Work.Slots t1
INNER JOIN TDCX.vCXComps t2
ON t1.PtnId=t2.ptnid
WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd
AND datepart(t1.DateTimeIn) BETWEEN datepart(t2.compDate) AND INTNX('month', datepart(t2.compDate), 1,'b');
QUIT;
Is a regular JOIN, not an inner join by default in SAS---was not aware, thanks
Your SQL looks syntactically correct but if it also will return your desired result depends on your source tables. We can't tell if you don't share representative sample data and show us the desired result.
My first question would be: In your table tdcx.vCXComps how many rows (dates) do you have per PtnId?
As for keyword JOIN used on its own: Yes, this is the same like INNER JOIN. I'd consider it a good practice to always use the INNER for clarity.
If you use Proc SQL option FEEDBACK then the SAS log will show you to what the SQL compiler transforms your code for execution.
In your case that's an implicit join using the WHERE clause only and with only AND conditions I assume it will return what you're after - but again: We can't really know without sample data and desired result.
28 proc sql feedback; 29 select sum(t1.theowin)/30 from work.slots t1 30 join tdcx.vcxcomps t2 31 on t1.ptnid=t2.ptnid 32 where datepart(t2.compdate) between '01jan2024'd and '31mar2024'd 33 and datepart(t1.datetimein) between datepart(t2.compdate) and intnx('month', datepart(t2.compdate), 1,'b'); NOTE: Statement transforms to: select SUM(T1.theowin) / 30 from WORK.SLOTS T1, TDCX.VCXCOMPS T2 where (T1.ptnid = T2.ptnid) and (DATEPART(T2.compdate) between '01JAN2024'D and '31MAR2024'D) and DATEPART(T1.datetimein) between DATEPART(T2.compdate) and INTNX('month', DATEPART(T2.compdate), 1, 'b'); 34 quit;
And just for completeness here the "proof" that SAS will convert JOIN to INNER JOIN
28 proc sql feedback; 29 select l.name 30 from sashelp.class l join sashelp.class r 31 on l.name=r.name 32 ; NOTE: Statement transforms to: select L.Name from SASHELP.CLASS L inner join SASHELP.CLASS R on L.Name = R.Name; 33 quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.