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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.