BookmarkSubscribeRSS Feed
LOK
Calcite | Level 5 LOK
Calcite | Level 5
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!
13 REPLIES 13
LinusH
Tourmaline | Level 20
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
Peter_C
Rhodochrosite | Level 12
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
LOK
Calcite | Level 5 LOK
Calcite | Level 5
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...
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
pervious row totals were provided by my second example
deleted_user
Not applicable
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
ChrisNZ
Tourmaline | Level 20
To read ahead, you just have to output late. 🙂

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;
Peter_C
Rhodochrosite | Level 12
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):(Column).
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
ChrisNZ
Tourmaline | Level 20
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;
Peter_C
Rhodochrosite | Level 12
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
ChrisNZ
Tourmaline | Level 20
Nice! So many ways to skin a cat Peter 🙂

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;
LOK
Calcite | Level 5 LOK
Calcite | Level 5
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...
LOK
Calcite | Level 5 LOK
Calcite | Level 5
Hi, Sukanya E

Thank you! for your solution....

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
  • 13 replies
  • 8381 views
  • 0 likes
  • 6 in conversation