Hi,
i have a data set with 4 variable, data set is shown below:
Storeid ProdId Date SP
1 101 22/1/2013 18
1 101 23/1/2013 18
1 101 24/1/2013 19
1 101 25/1/2013 18
1 101 26/1/2013 19
1 101 27/1/2013 19
Require output is:
Storeid ProdId Date SP
1 101 22/1/2013 18
1 101 24/1/2013 19
1 101 25/1/2013 18
1 101 26/1/2013 19
How to retrieve particular record when price is increasing or decresing?
data want;
set have;
by date notsorted;
if sp=lag(sp) then delete;
run;
Here is a solution, one comment though. Before checking your solution I wrote _N_ > 1 before first.date, didn't think you would want the first record.
data have;
infile cards;
informat date ddmmyy10.;
format date mmddyy10.;
input Storeid$ ProdId$ Date SP;
cards;
1 101 22/1/2013 18
1 101 23/1/2013 18
1 101 24/1/2013 19
1 101 25/1/2013 18
1 101 26/1/2013 19
1 101 27/1/2013 19
;
run;
data want;
set have;
_l_sp = lag(sp);
by date notsorted;
if first.date and _l_sp > sp then _increase = 1;
if first.date and _l_sp < sp then _decrease = 1;
if _increase = 1 or _decrease = 1 then output;
drop _:;
run;
data want;
set have;
by date notsorted;
if sp=lag(sp) then delete;
run;
Another opinion:
data want;
set have;
by StoreID ProdID SP notsorted;
if first.sp;
run;
Edited a couple of times, but this should do the trick. Good luck.
And so what would the right outcome be for this data? And what do your solutions actually generate?
Storeid ProdId Date SP
1 101 22/1/2013 18
1 101 23/1/2013 18
1 101 24/1/2013 19
1 101 25/1/2013 18
1 101 26/1/2013 19
1 101 27/1/2013 19
1 102 22/1/2013 19
1 102 23/1/2013 20
1 102 24/1/2013 20
1 102 25/1/2013 21
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.