Desktop productivity for business analysts and programmers

Assign Quarter Per School Date Range

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Assign Quarter Per School Date Range

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!


Accepted Solutions
Solution
‎11-17-2016 12:59 PM
Grand Advisor
Posts: 17,396

Re: Assign Quarter Per School Date Range

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;

 

View solution in original post


All Replies
Grand Advisor
Posts: 17,396

Re: Assign Quarter Per School Date Range

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

Contributor
Posts: 41

Re: Assign Quarter Per School Date Range

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

Solution
‎11-17-2016 12:59 PM
Grand Advisor
Posts: 17,396

Re: Assign Quarter Per School Date Range

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;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 192 views
  • 0 likes
  • 2 in conversation