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 ... View more

        Converted dates into year4. format, still no luck. I am sorry, I am naive at this doing it for the first time.    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 ... View more

Still no luck, table created with 0 observations and 7 rows 311 proc sort data=work.a; 312 by order_date Cust_id;run; NOTE: There were 108 observations read from the data set WORK.A. NOTE: The data set WORK.A has 108 observations and 7 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 313 proc sql; 314 create table S as 315 select a.*, b.order_date as prior_order_date 316 from work.a as a, work.a as b 317 where a.Cust_id = b.Cust_id 318 and a.prod_id = b.prod_id 319 and b.order_date in (2014,2015) 320 and a.order_date in (2016,2017); NOTE: Table WORK.S created, with 0 rows and 8 columns. 321 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

95 proc sql; 96 create table want as 97 select a.*, b.order_date as prior_order_date 98 from work.a as a, work.a as b 99 where a.Cust_id = b.Cust_id 100 and a.Prod_id = b.Prod_id 101 and b.order_date in (2014,2015) 102 and a.order_date in (2016,2017); NOTE: Table WORK.WANT created, with 0 rows and 8 columns. 103 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

When I try it, it gives me the observations which took place after 2016. Irrespective of it being repeated in the previous year.   I am trying to exclude products ordered by a customers more than once.   Say for example in the attached excel sheet there are 10 products(highlighted in yellow) new to the customer(not ordered in their previous encounters). I want  those rows to be shown in the results     ... View more

Thanks,   but when I run the code it says work.have created with 0 rows and 8 columns  please I am also attaching a sample data set if you can have a look ... View more

Salesman_name Prod_id Order_date Cust_name Sales_id Cust_id Paymode_ID john 233 3/27/2017 Bill.N S1922 J231 1 john 235 3/27/2017 Bill.N S1922 J231 1 john 244 3/27/2017 Bill.N S1922 J231 1 john 245 3/27/2017 Bill.N S1922 J231 1 Bail 252 3/27/2017 Susan S1750 S19 1 Bail 257 3/27/2017 Susan S1750 S19 1 Chuck 262 3/27/2017 Areeb S2011 A116 1 Chuck 266 3/27/2017 Areeb S2011 A116 1 Chuck 419 3/27/2017 Areeb S2011 A116 1 Chuck 420 3/27/2017 Areeb S2011 A116 1 phoung 428 3/28/2017 Michelle S1872 M103 3 Chuck 431 3/28/2017 Liz S2011 L103 2 Chuck 434 3/28/2017 Liz S2011 L103 2 Christy 443 3/28/2017 Laxmi S1881 L72 2 Christy 446 3/28/2017 Laxmi S1881 L72 2 john 455 3/29/2017 James S1922 J24 3 john 457 3/29/2017 James S1922 J24 3 john 460 3/29/2017 James S1922 J24 3 john 596 3/29/2017 James S1922 J24 3 phoung 603 3/29/2017 Rana S1872 R189 1   Couldn't copy the whole things, the data set has 53 variables and around 300,000 rows for 4 years.     ... View more

  incoming data is sales data for a store from 2014-2017.   Cust_id, order_date and sales id are character variables. Prod_id is numeric variable.   I want the results in a tabular form and to only include products that were new to customer(not ordered in the previous two years)   for example   Cust_id            prod_id                sales_id       order_date   A345                   74                      S231             09/09/2017     I am sorry, I am new to all this, is this what was expected ?? ... View more