topic Corresponding to LEAD and PARTITION BY in PROC SQL in SAS Programming

Corresponding to LEAD and PARTITION BY in PROC SQL

vaibhavpratap31 — Wed, 06 Nov 2019 09:56:51 GMT

Hi SAS community,

Need little help with a code . As LEAD and partition by does not works in PROC SQL . Not able to get the desired output.

Have a table like below

Id. Date Amount
1. 1-jan. 100
1. 2-jan. 100
1. 3-jan. 500
1. 4-jan. 100
1. 5-jan. 200
1. 6-jan. 200

We need to create a separate table which will have start date and end date based on the change of amount. So the desired output needed is

Id. Start Date End Date Amount
1. 1-jan. 2-jan. 100
1. 3-jan. 3-jan. 500
1. 4-jan. 4-jan. 100
1. 5-jan. 6-jan. 200

We only need to use PROC SQL to get the desired result.Thanks for the help in advance.

Thanks

Re: PROC SQL

PeterClemmensen — Wed, 06 Nov 2019 06:45:45 GMT

@vaibhavpratap31 and welcome to the SAS Community 🙂

Does it have to be done in PROC SQL? Seems easier in a data step like this

data have;
input Id Date $ Amount;
datalines;
1 1-jan 100
1 2-jan 100
1 3-jan 500
1 4-jan 100
1 5-jan 200
1 6-jan 200
;

data want(drop=Date);
    format Id Start_Date Last_Date Amount;
    do _N_=1 by 1 until (last.Amount);
        set have;
        by Amount notsorted;
        if _N_=1 then Start_Date=Date;
    end;
    Last_Date=Date;
run;

Result:

Id  Start_Date  End_Date  Amount
1   1-jan       2-jan     100
1   3-jan       3-jan     500
1   4-jan       4-jan     100
1   5-jan       6-jan     200

Re: PROC SQL

Kurt_Bremser — Wed, 06 Nov 2019 07:02:23 GMT

SQL is not suited for look-back/look-ahead issues, the data step is the tool of choice (see Maxim 14). See another approach using mechanisms supplied by the data step:

data want;
set have;
by id amount notsorted;
retain start_date;
if first.amount then start_date = date;
if last.amount
then do;
  end_date = date;
  output;
end;
keep id start_date end_date amount;
run;

@vaibhavpratap31 wrote:
Hi SAS community,

Need little help with a code . As LEAD and partition by does not works in PROC SQL . Not able to get the desired output.

Have a table like below

Id. Date Amount
1. 1-jan. 100
1. 2-jan. 100
1. 3-jan. 500
1. 4-jan. 100
1. 5-jan. 200
1. 6-jan. 200

We need to create a separate table which will have start date and end date based on the change of amount. So the desired output needed is

Id. Start Date End Date Amount
1. 1-jan. 2-jan. 100
1. 3-jan. 3-jan. 500
1. 4-jan. 4-jan. 100
1. 5-jan. 6-jan. 200

We only need to use PROC SQL to get the desired result.Thanks for the help in advance.

Thanks