Selecting previous two working days

[Sandeep_Gupta]

Hi Everyone,

I want to select records where product code is B and previous two working dates. For stock ICICBAN i want 8jun2015 and previous two working days 3jun2015(3 entris) 5jun2015(2 entries).For stock code TISCO, I want 9Jun and 8Jun2015. For IPCLAB,I want 12may2015,11may2015 and 8may2015.

Kindly help me out in coding this problem.

Regards

Sandeep Gupta

Date	STOCK	PRODUCT_CODE
3-Jun-15	ICIBAN	C
3-Jun-15	ICIBAN	C
3-Jun-15	ICIBAN	C
5-Jun-15	ICIBAN	C
5-Jun-15	ICIBAN	C
8-Jun-15	ICIBAN	B
14-May-15	INFTEC	C
14-May-15	INFTEC	C
18-May-15	INFTEC	B
18-May-15	INFTEC	B
5-Jun-15	INFTEC	C
5-Jun-15	INFTEC	C
9-Jun-15	INFTEC	C
15-Jun-15	INFTEC	C
23-Jun-15	INFTEC	C
8-Jul-15	INFTEC	C
21-Jul-15	INFTEC	C
7-May-15	IPCLAB	C
8-May-15	IPCLAB	C
11-May-15	IPCLAB	C
12-May-15	IPCLAB	B
8-Jun-15	TISCO	C
9-Jun-15	TISCO	B

[ChrisNZ]

data HAVE;
  input DATE date. STOCK $7. PRODUCT_CODE : $1.;
  format DATE date.;
  cards;
3Jun15	ICIBAN C
3Jun15	ICIBAN C
3Jun15	ICIBAN C
5Jun15	ICIBAN C
5Jun15	ICIBAN C
8Jun15	ICIBAN B
14May15	INFTEC C
14May15	INFTEC C
18May15	INFTEC B
18May15	INFTEC B
5Jun15	INFTEC C
5Jun15	INFTEC C
9Jun15	INFTEC C
15Jun15	INFTEC C
23Jun15	INFTEC C
8Jul15	INFTEC C
21Jul15	INFTEC C
7May15	IPCLAB C
8May15	IPCLAB C
11May15	IPCLAB C
12May15	IPCLAB B
8Jun15	TISCO  C
9Jun15	TISCO  B
run;
proc sort nodupkey ;
  by STOCK PRODUCT_CODE DATE;
run;
data WANT;
  set HAVE;
  by STOCK PRODUCT_CODE DATE;
  if first.STOCK then do;
    if PRODUCT_CODE ne 'B' then put 'ERROR' _ALL_;
    N=0;
  end;
  N+1;
  if N<=3 then output; 
run;

[ChrisNZ]

Or if you need to keep the number of records for each date:

data HAVE;
  input DATE date. STOCK $7. PRODUCT_CODE : $1.;
  format DATE date.;
  cards;
3Jun15	ICIBAN C
3Jun15	ICIBAN C
3Jun15	ICIBAN C
5Jun15	ICIBAN C
5Jun15	ICIBAN C
8Jun15	ICIBAN B
14May15	INFTEC C
14May15	INFTEC C
18May15	INFTEC B
18May15	INFTEC B
5Jun15	INFTEC C
5Jun15	INFTEC C
9Jun15	INFTEC C
15Jun15	INFTEC C
23Jun15	INFTEC C
8Jul15	INFTEC C
21Jul15	INFTEC C
7May15	IPCLAB C
8May15	IPCLAB C
11May15	IPCLAB C
12May15	IPCLAB B
8Jun15	TISCO  C
9Jun15	TISCO  B
run;
proc means data=HAVE nway noprint;
  class STOCK PRODUCT_CODE DATE;
  output out=SUM(drop=_TYPE_);
run;
data WANT;
  set SUM;
  by STOCK PRODUCT_CODE DATE;
  if first.STOCK then do;
    if PRODUCT_CODE ne 'B' then put 'ERROR' _ALL_;
    N=0;
  end;
  N+1;
  if N<=3 then output; 
run;

[ChrisNZ]

Or if you need to keep each record replace

  if N<=3 then output; 

with

  if N<=3 then do i=1 to _FREQ_; output; end;

[Astounding · Accepted solution]

It's a much more difficult problem than it seems at first, because of the possibility of multiple "B" records for the same product.

I recommend that you begin by numbering the dates for each product:

data have2;

   set have;

   by stock date;

   if first.stock then counter=1;

   else if first.date then counter + 1;

run;

You can always print this to see what it looks like.

That makes it relatively easy to get a list of all the counter numbers that you would want:

data have3;

   set have2;

   where product_code='B';

   do needed_counter = max(1, counter-2) to counter;

      output;

   end;

   keep stock needed_counter;

run;

proc sort data=want3 nodupkey;

   by stock needed_counter;

run;

Again, you can print the results to see what you are getting.

Finally, merge and select:

data want;

   merge have2 (in=in1) have3 (in=in2 rename=(needed_counter=counter));

    by stock counter;

   if in1 and in2;

run;

There may be ways to do this with SQL instead, but you would have to be very careful not to select the same record twice if, for example, there were a pattern of B C B on three sequential records for the same product.

[Sandeep_Gupta]

Thanks a lot guys...