Selecting previous two working days

Solved
Occasional Contributor
Posts: 6

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

 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

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

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.

All Replies
PROC Star
Posts: 2,363

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: 2,363

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: 2,363

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: 6,774

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