topic Re: Scenario on extracting rows by group in SAS Programming

Scenario on extracting rows by group

Vigneswar — Thu, 16 Dec 2021 16:29:30 GMT

Hi All,

I have the below scenario,

Input:

ID	utp	start date	end date
123	0	1/1/2021	31/1/2021
123	0	1/2/2021	10/3/2021
123	0	11/3/2021	30/4/2021
123	1	1/5/2021	24/5/2021
123	1	25/5/2021	4/6/2021
123	1	5/6/2021	20/6/2021
123	0	21/6/2021	1/7/2021
123	0	2/7/2021	9/7/2021
123	0	10/7/2021	21/7/2021
123	0	22/7/2021	14/8/2021

Output:

ID	utp	start date	end date
123	0	1/1/2021	30/4/2021
123	1	1/5/2021	20/6/2021
123	0	21/6/2021	14/8/2021

Can anyone please suggest an approach to retreive the above output based on the prescribed input?

Any suggestions are highly appreciable!!

Thanks in advance!!

Re: Scenario on extracting rows by group

PaigeMiller — Thu, 16 Dec 2021 16:33:00 GMT

Please explain the logic that allows you to derive the output data from the input data.

Re: Scenario on extracting rows by group

Vigneswar — Thu, 16 Dec 2021 16:43:41 GMT

Hi, thanks for your response!!

In this example, there are 3 combinations that need to be extracted. Within a ID group, there are 2 possible utp values and different start and end dates. Row 1 to 3, 4 to 6, 7 to 10 there are 3 combinations. So mainly I want to extract based on ID group, utp and dates. In my output (first row) ID 123 and utp 0 are combined with start date from 1st row and end date from 3rd row. Likewise, 2nd row in the output is combined with start date from 4th row and end date in 6th row. 3rd row in the output is combined with start date from 7th row and end date in 10th row.

To summarize, we need extract based on (ID and utp) group and extract their start and end date from different rows.

Please let me know if you need more clarity.

Re: Scenario on extracting rows by group

Reeza — Thu, 16 Dec 2021 16:52:02 GMT

Use the NOTSORTED option on the BY statement.

data want;
set have;
retain start;

by id utp NOTSORTED;
if first.utp then Start = start_date;
if last.utp then do;
end = end_date;
output;
end;

format start end date9.;
drop start_date end_date;
run;

Re: Scenario on extracting rows by group

PaigeMiller — Thu, 16 Dec 2021 17:01:16 GMT

So you want the minimum of the start_date and the maximum of the end_date for each grouping of the variables ID and UTP?

Or do you want always the first row start_date and the last row end_date for each grouping of the variables ID and UTP?

Re: Scenario on extracting rows by group

Vigneswar — Thu, 16 Dec 2021 17:52:07 GMT

Thank you so much!!