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 |
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.
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;
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;
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;
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.
Thanks a lot guys...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.