Help using Base SAS procedures

How to add Previous observation values

Reply
Occasional Contributor LOK
Occasional Contributor
Posts: 11

How to add Previous observation values

Hi,
I want to check the id value equal to 4 or not, if it is 4 then i require sum of id and age of previous observation...Example as follows

Input Data:

data dat1;
input id age;
cards;
1 10
2 10
3 10
4 10
5 10
6 10
4 10
;
run;


Output:

id age total
1 10
2 10
3 10 13
4 10
5 10
6 10 16
4 10

Provide me sas code for this query...
Thankyou!
Super User
Posts: 5,254

Re: How to add Previous observation values

Apart from not at all understand why you want to do this...I guess the sort order here is important. SAS has ways to keep (retain)/get (lag) values from previous observations. But in your example it seems that you need to read ahead. So, if you don't have any other id variable that describe the original sort order, you might first have to loop thru the data and store a sort order id variable (using _n_). Then sort the data in reverse order, and then use retain or lag to do your conditional calculation. And finally, sort your data back to the original order.

/Linus
Data never sleeps
Valued Guide
Posts: 2,174

Re: How to add Previous observation values

previous data step iteration values are available before they are replaced in a SET statement. The following step provides the total of current observation ID with preceding observation AGE[pre] data second_data ;
last_age = age ;
set first_data ;
if id = 4 then total = last_age + id ;
else total = . ;
run ;[/pre] Perhaps I misunderstand, and you want total calculated entirely from values in the preceding observation. That is simpler[pre] data another_way ;
total = age + ID ;
set first_data ;
if id ne 4 then total = . ;
run ;[/pre]
One effect of this approach is that missing values are present before the first SET statement, and a message would be generated about missing values. If you need to avoid that, in the second demo, derive [pre] total = sum( age, ID, 0 );[/pre] and for the first [pre] if id = 4 then total = sum( last_age, id, 0 ) ;[/pre]
Does this provide what was wanted ?

PeterC
Occasional Contributor LOK
Occasional Contributor
Posts: 11

Re: How to add Previous observation values

Hi,
Peter.C

I like to add previous observation values, not current observation values...

Example If Current observation ID value is 4 then I require Sum for Previous or last obsevation.

Thanks...
Super Contributor
Super Contributor
Posts: 3,174

Re: How to add Previous observation values

Consider that Peter.C provide you with a "DATA step structure" to work with - did you consider taking what's been provided and working with it to address your requirement, rather than simply asking again for a solution to be coded for you?

Break open the SAS documentation, particularly the SAS LANGUAGE guide and explore using the LAG function in an assignment statement, and also (as demonstrated) using a SUM function technique to accumulate your "total" variable until a suitable condition occurs, as you have done well to explain your basic, fundamental program requirement.

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,174

Re: How to add Previous observation values

pervious row totals were provided by my second example
N/A
Posts: 0

Re: How to add Previous observation values

Try the following code. It is lengthy but solves your problem. Let me know if it doesn't work.

data dat1;
input id age;
cards;
1 10
2 10
3 10
4 10
5 10
6 10
4 10
;
run;

data dat2;
set dat1;
retain prev_id prev_age;
if id=4 then total=prev_id+prev_age;
else do;
prev_id=id;
prev_age=age;
end;
seq+1;
run;

proc sort data=dat2;
by descending seq;
run;

data dat3(drop=prev_id prev_age prev_tot);
set dat2;
retain prev_tot ;
by total notsorted;
if total ne . then do;
prev_tot=total;
total=.;
end;
else total=prev_tot;
if not first.total then total=.;
run;

proc sort data=dat3 out=dat4(drop=seq);
by seq;
run;


~ Sukanya E
PROC Star
Posts: 1,558

Re: How to add Previous observation values

To read ahead, you just have to output late. Smiley Happy

This works:
[pre]
data t;
retain SUM 0;
set DAT1 end=LASTOBS;
ID1=lag(ID);
AGE1=lag(AGE);
if _N_ ne 1 then put ID1 AGE1 @;
if ID=4 then do;
SUM+AGE;
put SUM;
SUM=0;
end;
else put;
SUM+ID;
if LASTOBS then put ID AGE;
run;
Valued Guide
Posts: 2,174

Re: How to add Previous observation values

an alternative : equivalent to "output late" is "read late"
[pre] data alternative_way ;
total = age + ID ;
set first_data ;
if id ne 4 then total = . ;
run ;[/pre]Since there seems to be some doubt about this approach, I'll post the listing[pre]PeterC alternative

Obs total ID age

1 . 1 12
2 . 2 34
3 . 3 56
4 59 4 78
5 . 5 42
6 . 6 51
7 57 4 60[/pre]


and here is my log (using LIST; to show my alternative sample data)[pre]65 data first_data ;
66 input id age;
67 list;cards;

RULE: ----+----1----+----2----+----3----+----4----+----5----+----
68 1 12
69 2 34
70 3 56
71 4 78
72 5 42
73 6 51
74 4 60
NOTE: The data set WORK.FIRST_DATA has 7 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 144k


75 ;
76 run;
77
78 data alternative_way ;
79 total = age + ID ;
80 set first_data ;
81 if id ne 4 then total = . ;
82 run ;

NOTE: Missing values were generated as a result of performing an
operation on missing values.
Each place is given by: (Number of times) at (Line)Smiley SadColumn).
1 at 79:19
NOTE: There were 7 observations read from the data set
WORK.FIRST_DATA.
NOTE: The data set WORK.ALTERNATIVE_WAY has 7 observations and 3
variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 181k


83 title 'PeterC alternative ' ;
84 proc print ; run;

NOTE: There were 7 observations read from the data set
WORK.ALTERNATIVE_WAY.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.53 seconds[/pre]
predictably, a missing value was generated for the first observation, as no previous observation is present.

I thought the brevity of the method merited repetition.
The calculation of TOTAL is made upon the values SET from the input data set on the prior iteration. By the nature of a DATA STEP these are "retained".

This seemed a suitable request to examine and demonstrate the feature.

PeterC
PROC Star
Posts: 1,558

Re: How to add Previous observation values

Good thinking Peter!
For LOK's benefit, the example becomes
[pre]
data t;
if _N_ ne 1 then put ID AGE @;
set DAT1 end=LASTOBS;
if ID=4 then do;
SUM+AGE;
put SUM;
SUM=0;
end;
else put;
SUM+ID;
run;
Valued Guide
Posts: 2,174

Re: How to add Previous observation values

thanks Chris
I saw a nice scheme suitable for this problem, at our local SAS user S.T.U.D.I.O
The design might be one you're familiar with but is worth repeating.
Use a MERGE without BY to merge next observation with current. For this example, it would look like:[pre] data t ;
merge dat1 dat1( firstObs= 2 keep= ID rename=( ID=nextID ) ) ;
if nextID = 4 then total = id + age ;
put id age total ;
run ;[/pre]

so thanks are properly due to the presenter last night Jason Duncan-King, Lead Architect at Chambers & Smeaton, who hosted the event.
Thank you Jason

PeterC
PROC Star
Posts: 1,558

Re: How to add Previous observation values

Nice! So many ways to skin a cat Peter Smiley Happy

LOK, You just have to make a small change to alter the way the sum is made in my previous post. The principle stays the same if you try to understand it.
[pre]
data t;
if _N_ ne 1 then do;
put ID AGE @;
SUM=ID+AGE;
end;
set DAT1 end=LASTOBS;
if ID=4 then put SUM;
else put;
run;
Occasional Contributor LOK
Occasional Contributor
Posts: 11

Re: How to add Previous observation values

data dat1;
input id age;
cards;
1 10
2 10
3 10
4 10
5 10
6 10
4 10
;
run;


Output:

id age total
1 10
2 10
3 10 13
4 10
5 10
6 10 16
4 10

Hi, please check the out put i require same order, if id is 4 then it should add id and age of previous observation and it should show in previous observation only...
Occasional Contributor LOK
Occasional Contributor
Posts: 11

Re: How to add Previous observation values

Hi, Sukanya E

Thank you! for your solution....
Ask a Question
Discussion stats
  • 13 replies
  • 1952 views
  • 0 likes
  • 6 in conversation