Assign Quarter Per School Date Range

[carmendee]

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!

[Reeza]

Can you illustrate your problem with a small set of data in a post. Many users cannot download XLSX files due to security restrictions.

[carmendee]

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

[Reeza · Accepted solution]

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;