Attached spreadsheet has two input files (File_1 & File_2) and a desired result.
I need to create a table with the Quarter Per School to be added to File_1 for a date in the year
according to date range (File_2) for that school.
Cannot join on the schoolid so assuming I need to write a proc.
Help!
I think a SQL join here is straight forward:
proc sql;
create table want as
select a.*, coalescec(b.quarter, 'NA') as Quarter
from file_1 as a
left join file_2 as b
on a.calendar_date between b.date1 and b.date2
and a.school=b.schoolID;
quit;
Can you illustrate your problem with a small set of data in a post. Many users cannot download XLSX files due to security restrictions.
Thanks Reeza!
File_1
CALENDAR_DATE SCHOOL
04Mar2017 0:00:00 340326
04Mar2017 0:00:00 340330
04Mar2017 0:00:00 340504
11Feb2017 0:00:00 340326
11Feb2017 0:00:00 340330
11Feb2017 0:00:00 340504
10Jan2017 0:00:00 340326
10Jan2017 0:00:00 340330
10Jan2017 0:00:00 340504
01Jul2016 0:00:00 340326
01Jul2016 0:00:00 340330
01Jul2016 0:00:00 340504
File_2
Quarter DATE1 DATE2 SCHOOLID
Q1 29Aug2016 0:00:00 08Nov2016 0:00:00 340326
Q1 29Aug2016 0:00:00 07Nov2016 0:00:00 340330
Q1 29Aug2016 0:00:00 08Nov2016 0:00:00 340504
Q2 09Nov2016 0:00:00 20Jan2017 0:00:00 340326
Q2 09Nov2016 0:00:00 21Jan2017 0:00:00 340330
Q2 09Nov2016 0:00:00 20Jan2017 0:00:00 340504
Q3 24Jan2017 0:00:00 02Apr2017 0:00:00 340326
Q3 24Jan2017 0:00:00 02Apr2017 0:00:00 340330
Q3 24Jan2017 0:00:00 02Apr2017 0:00:00 340504
Q4 03Apr2017 0:00:00 07Jun2017 0:00:00 340330
Q4 03Apr2017 0:00:00 08Jun2017 0:00:00 340326
Q4 03Apr2017 0:00:00 08Jun2017 0:00:00 340504
Results
CALENDAR_DATE SCHOOL Quarter
04Mar2017 0:00:00 340326 Q3
04Mar2017 0:00:00 340330 Q3
04Mar2017 0:00:00 340504 Q3
11Feb2017 0:00:00 340326 Q3
11Feb2017 0:00:00 340330 Q3
11Feb2017 0:00:00 340504 Q3
10Jan2017 0:00:00 340326 Q2
10Jan2017 0:00:00 340330 Q2
10Jan2017 0:00:00 340504 Q2
01Jul2016 0:00:00 340326 NA
01Jul2016 0:00:00 340330 NA
01Jul2016 0:00:00 340504 NA
I think a SQL join here is straight forward:
proc sql;
create table want as
select a.*, coalescec(b.quarter, 'NA') as Quarter
from file_1 as a
left join file_2 as b
on a.calendar_date between b.date1 and b.date2
and a.school=b.schoolID;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.