How to get product count which are new to customers?

[VDD]

1st. switch your sort order to be by cust_id  order_date.

your question is customer based on order date.

@ihtishamsultan

383 proc sort data=work.year;

384 by order_date cust_id;run;

@VDD

383 proc sort data=work.year;

384 by cust_id order_date ;run;

2nd. ensure the prior year is the correct year value you want. 

do you really want to look back 2 years or can you look back only 1 year?  

@ihtishamsultanhas 2015 for prior lookup records in 2017, but in your original request criteria 2014 and 2015 where the prior years to look for when finding current records for 2016 and 2017. 

@ihtishamsultan

391 and b.order_date in (2015)

392 and a.order_date in (2017);

VDD says Try this

391 and b.order_date in (2014,2015)

392 and a.order_date in (2017);

you may not want to look back 3 years based on the original requirements statement in your request.  If not then drop the 2014 that I have above.

[ihtishamsultan]

Thank you for being patient.

I do not currently have access to the orignal dataset the person gave me this sample dataset to try the code on, this data set has entries from 2017 and 2015 to simplify it.

as per your advise I sorted the dataset by cust_id and then order_date; and tried running it again and it gives me 0 rows and 7 columns.

379 data year;

380 set work.a;

381 format order_date year4.;

382 run;

NOTE: There were 108 observations read from the data set WORK.A.

NOTE: The data set WORK.YEAR has 108 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

　

383 proc sort data=work.year;

384 by order_date cust_id;run;

NOTE: There were 108 observations read from the data set WORK.YEAR.

NOTE: The data set WORK.YEAR has 108 observations and 7 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.03 seconds

　

385 proc sql;

386 create table japan as

387 select a.*, b.order_date as prior_order_date

388 from work.year as a, work.year as b

389 where a.Cust_id = b.Cust_id

390 and a.Prod_id = b.Prod_id

391 and b.order_date in (2015)

392 and a.order_date in (2017);

NOTE: Table WORK.JAPAN created, with 0 rows and 8 columns.

393 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds