I would like to have the variable VAR in table1 modified in a way that
for each TIME = i CATEGORY VAR(i) le (VAR(i-1) holds.
I have made various attempts to achieve this, but there must be some lack of understanding or conceptional mistake in my thinking.
My most naive approach was:
data table2;
set table1;
if CATEGORY = lag(CATEGORY) then VAR = MIN(VAR, lag(VAR));
run;
But this obiously does not work.
Any ideas?
Thanks.
Gregor
PS. I would have liked to post my example as picture, sas file, excel or somehow, but the forum only excepts unfitting formats
(.bin, .dms, .lzh, .exe, .class, .so, .dll).
In which case for retain, retain another variable which keeps the maximum value so far:
data table2 (drop=lst_val);
set table1;
retain lst_val;
by category;
if first.category=1 then lst_val=var;
else do;
if var > lst_val then lst_val=var;
else var=lst_val;
end;
run;
So lst_val will increase within the group of category where var > lst_val. If var is less than lst_val then it becomes lst_val.
Couldn't test, but I think:
Data table2;
If _N_ ge 2 Then Set table1(Firstobs=1 Keep=Category Var Rename=(Var=Var_Lag Category=Category_Lag));
Set table1;
If Category eq Category_Lag Then Var=Min(Var,Var_Lag);
Run;
Thanks, but this does not work in my case when I had (within the same category)
VAR(5) = -8%
VAR(6) = -7%
VAR(7) = -5%
VAR(8) = -6%
VAR(9) = -3%
Then I get with your solution:
VAR(5) = -8%
VAR(6) = -8%
VAR(7) = -7%
VAR(8) = -6%
VAR(9) = -6%
But I want them all to be -8%
Could it be a problem that my categories are alpha-numeric?
This was an answer to user24feb. RW9s solution does not change any of the five numbers.
Maybe I did not explain myself well enough.
I shall copy my input table:
CATEGORY TIME VAR
A 0 0%
A 1 -1%
A 2 -3%
A 3 -5%
A 4 -5%
A 5 -5%
A 6 -10%
A 7 -10%
A 8 -10%
A 9 -2%
B 0 0%
B 1 0%
B 2 -3%
B 3 -5%
B 4 -6%
B 5 -8%
B 6 -7%
B 7 -5%
B 8 -6%
B 9 -3%
After the data step, I want all category B from TIME 5 on to be -8%, A TIME 9 to be -10%
Maybe I can program it with a macro loop, but I thought there might be a more elegant way.
In which case for retain, retain another variable which keeps the maximum value so far:
data table2 (drop=lst_val);
set table1;
retain lst_val;
by category;
if first.category=1 then lst_val=var;
else do;
if var > lst_val then lst_val=var;
else var=lst_val;
end;
run;
So lst_val will increase within the group of category where var > lst_val. If var is less than lst_val then it becomes lst_val.
Works if I change the > to <. Thanks a lot.
Assuming your data is right, then you could use:
data table2;
set table1;
retain lst_cat lst_val;
if _n_=1 then do;
lst_cat=category;
lst_val=.;
end;
else do;
if CATEGORY = lst_cat then VAR = MIN(VAR, lst_var);
lst_cat=category;
lst_var=var;
end;
run;
Not tested as no test/output given. Note that its easier for us if you post test data in a datastep in the post, and give example output as text in the post.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.