Using do group within if/then/else

Reply
Frequent Contributor
Frequent Contributor
Posts: 134

Using do group within if/then/else

Can someone help me figure out why I am not getting expected results with my code. I want to assign variables to the first id in the by group and then use else if for all ids to assign the variables. The priornew does not get changed to missing for the next new id. New is being created but the prior record value needs to be used in some of the if/then conditions. I have a more complicated task but the following sample code explains my problem more simply. This sample seems to create the right value for the code. In addition to my question above in my complicated scenario instead of adding .01 to the new variable I am getting results with .06 or .07 added so I think the do/ends might be interative based on using a by variable - though it's not doing it here. 

 

data have ;
input id d ;
datalines ;
1001 3
1001 9
1002 4
1003 5
1003 4
1003 4
;
run;
proc sort data=have ;
by id ;
run;
data want ;
set have ;
by id ;

if first.id then do;
                     priornew=. ;
                     new=d+.01 ;
                     rule = 1 ;
                    end ;
else if d >= 5 and priornew < 20 then do;
                                                          new=d+1 ;
                                                          rule = 2 ;
                                                           end;
else if d = 1 then do ;
                           new=d+10 ;
                           rule = 3 ;
                           end;
else new = d ;

priornew = lag(new) ;

run;
proc print; run;

 

results.PNG

 

 

Super User
Super User
Posts: 7,955

Re: Using do group within if/then/else

What do you expect the output to look like?

Frequent Contributor
Frequent Contributor
Posts: 134

Re: Using do group within if/then/else

priornew should be missing for obs 3 and 4
Super User
Super User
Posts: 7,046

Re: Using do group within if/then/else

PRIORNEW is not missing on new id's because you are setting to LAG(new).

 

I think what you want might be to RETAIN NEW and then set PRIORNEW=NEW at the top of the data step instead.

Frequent Contributor
Frequent Contributor
Posts: 134

Re: Using do group within if/then/else

tried this first but it didn't work. I think that retain keeps the most current value which might have been obtained from a different if statement rather than from the row above.
Frequent Contributor
Frequent Contributor
Posts: 134

Re: Using do group within if/then/else

also I reset priornew to missing at the beginning with the first.id variable so that is why I expected observations 3 and 4 to be missing for priornew. Thanks for your suggestions. maybe i will give retain another try.
Frequent Contributor
Frequent Contributor
Posts: 134

Re: Using do group within if/then/else

ah - I see. You are right. it gets reassigned at the end. Ugh! Thank you.
Respected Advisor
Posts: 4,920

Re: Using do group within if/then/else

Please confirm @Tom's suggestion as correct to wrap the issue.

PG
Frequent Contributor
Frequent Contributor
Posts: 134

Re: Using do group within if/then/else

Tom's suggestion explained why priornew was reassigned but when I move it to the top all records are show missing data and not just the first occurrence of id. I am still unable to create a new variable and reference the prior value of it. I know it shouldn't work exactly like Excel but I have to figure out a way to convert this Excel task to SAS. Still working on it...
Super User
Super User
Posts: 7,046

Re: Using do group within if/then/else

What do you want the output to look like?

data want ;
set have ;
by id ;
priornew = new ;
retain new;
...
run;

Capture.PNG

Ask a Question
Discussion stats
  • 9 replies
  • 504 views
  • 0 likes
  • 4 in conversation