DATA Step, Macro, Functions and more

Selecting previous two working days

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Selecting previous two working days

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

DateSTOCKPRODUCT_CODE
3-Jun-15ICIBANC
3-Jun-15ICIBANC
3-Jun-15ICIBANC
5-Jun-15ICIBANC
5-Jun-15ICIBANC
8-Jun-15ICIBANB
14-May-15INFTECC
14-May-15INFTECC
18-May-15INFTECB
18-May-15INFTECB
5-Jun-15INFTECC
5-Jun-15INFTECC
9-Jun-15INFTECC
15-Jun-15INFTECC
23-Jun-15INFTECC
8-Jul-15INFTECC
21-Jul-15INFTECC
7-May-15IPCLABC
8-May-15IPCLABC
11-May-15IPCLABC
12-May-15IPCLABB
8-Jun-15TISCOC
9-Jun-15TISCOB

Accepted Solutions
Solution
‎09-29-2015 12:14 AM
Super User
Posts: 5,099

Re: Selecting previous two working days

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.

View solution in original post


All Replies
PROC Star
Posts: 1,567

Re: Selecting previous two working days

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;
PROC Star
Posts: 1,567

Re: Selecting previous two working days

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;
PROC Star
Posts: 1,567

Re: Selecting previous two working days

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;
Solution
‎09-29-2015 12:14 AM
Super User
Posts: 5,099

Re: Selecting previous two working days

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.

Occasional Contributor
Posts: 6

Re: Selecting previous two working days

Thanks a lot guys...

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 215 views
  • 0 likes
  • 3 in conversation