BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gregor
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

6 REPLIES 6
user24feb
Barite | Level 11

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;

Gregor
Obsidian | Level 7

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?

Gregor
Obsidian | Level 7

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.

     

         

          


RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Gregor
Obsidian | Level 7

Works if I change the > to <.   Thanks a lot.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 987 views
  • 0 likes
  • 3 in conversation