Hello All,
I am attaching the snap here
So in above column, Seriesvar has 3 variable , F1,F2,F3 . So for each seriesVar, first we need to check that if in the first row of each variable , quantity is 0 then we need to select 4 rows below that and if first row of series var is not 0 then select 4 rows from starting of that seriesvar. I have highlighted the rows which I want as output. Month is also sorted in descending order so we can put logic in this way also like if Max month of a seriesVar have quantity 0 , then skip that row and select next 4 rows of that seriesvar. it should be by series Var.
I am struggling to write code for this.
Can anyone help me on this.
Thanks
data have;
input id quantity;
cards;
1 0
1 3
1 4
1 3
1 5
1 6
2 4
2 3
2 0
2 3
2 6
2 6
;
data temp;
set have;
by id;
retain found 0;
if first.id then found=0;
if quantity ne 0 then found=1;
run;
data temp;
set temp;
by id found;
if first.found then n=0;
n+1;
run;
data want;
set temp;
if found and n in (1:4);
run;
Hello @priyanka14 Please post the sample as plain text or in the datastep format so that community members can copy paste easily rather than pics.
Would you also be kind enough to rename your original post so there is a meaningful title, such as "Selecting Certain Rows"?
data have;
input id quantity;
cards;
1 0
1 3
1 4
1 3
1 5
1 6
2 4
2 3
2 0
2 3
2 6
2 6
;
data temp;
set have;
by id;
retain found 0;
if first.id then found=0;
if quantity ne 0 then found=1;
run;
data temp;
set temp;
by id found;
if first.found then n=0;
n+1;
run;
data want;
set temp;
if found and n in (1:4);
run;
data have;
input id quantity;
cards;
1 0
1 3
1 4
1 3
1 5
1 6
2 4
2 3
2 0
2 3
2 6
2 6
;
data want;
do _n_=0 by 1 until(last.id);
set have;
by id;
if _n_=0 and quantity ne 0 then _n_=1;
if _n_ in (1:4) then output;
end;
run;
Is there ever an occurrence where one of the SeriesVar groups will not have 4 records? This is potentially important as if you select 4 records when the SeriesVar group has fewer than 4 records then you will 1) select records from a different group and 2) proabaly miss the proper start for the next group (since the records were selected for the previous group).
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.