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

Search the site...

Geo-targeting Project [21-25]

The list of postcodes must be exported to a csv file for the marketing team.

This can be done using Proc Export.

proc export data=target_postcode
outfile='/folders/myfolders/Geo-targeting/output/target_postcode.csv'
dbms=csv
replace;
run;

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;

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;

The file is exported to a csv file in the output folder:

Note: the file is exported to the OUTPUT folder, not the DATA FILES folder. Both folders are in the ‘Geo-targeting’ folder that you downloaded at the beginning of this project.