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
@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
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
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.