Retail Projects 1 - Laptop Sales Analysis Intro 1 - Section 120

Search the site...

Sentry Page Protection

Geo-targeting Project [20-25]

We will now generate the list of postcodes that are within 4.5 km of the three stores that we selected.

The code below finds all the postcodes that are within 4.5 km of the stores:

proc sql;
create table target_postcode as
select a.*,
round(((b.os_x - a.os_x)**2 + (b.os_y - a.os_y)**2) **0.5, 1) as dist
from all_postcode a, locations b
where b.postcode in
(select store_postcode
from target) and
calculated dist <= 4500;
quit;

Did the code fail?

Copy and run the code below to create the input data sets.

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/Sales_Q1.csv"
 out = Sales_Q1
 dbms = CSV
 replace
 ;
run;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/locations.csv"
 out = locations
 dbms = CSV
 replace
 ;
run;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/postcode.csv"
 out = postcode
 dbms = CSV
 replace
 ;
run;

proc sql;
create table sales_q1_loc as
select a.*, b.os_x as store_x, b.os_y as store_y
from sales_q1 a, locations b
where a.store_postcode = b.postcode;
quit;

proc sql;
create table sales_q1_loc_pos as
select a.*, b.os_x as cust_x, b.os_y as cust_y
from sales_q1_loc a left join postcode b
on a.customer_postcode = b.postcode;
quit;

filename postcode '/folders/myfolders/Geo-targeting/Data Files/open_postcode_geo.csv';

data all_postcode (keep=postcode os_x os_y);
infile postcode dsd firstobs=2;
input postcode : $8.
      col1 : $10.
      col2 : $5.
      os_x os_y
      col3 
      area : $8.;
if area = "England" and os_x^=.;
run;

proc sql;
create table sales_q1_loc_pos2 as
select a.*, b.os_x as cust_x1, b.os_y as cust_y1
from sales_q1_loc_pos a left join all_postcode b
on a.customer_postcode = b.postcode;
quit;

data sales_q1_loc_pos3;
set sales_q1_loc_pos2;

if cust_x = . then cust_x = cust_x1;
if cust_y = . then cust_y = cust_y1;

drop cust_x1 cust_y1;
run;

data sales_q1_loc_pos4;
set sales_q1_loc_pos3;

distance = round(((store_x-cust_x)**2 + (store_y-cust_y)**2) **0.5, 1);

run;

proc sql;
create table target as
select distinct store_postcode
from sales_q1_loc_pos4
where store_postcode in ('E7 8NW' 'N17 6QA' 'CR7 8LE');
quit;

There are 42408 postcodes that fall within the range of 4.5 km from the selected stores: