Solved
Contributor
Posts: 65

Function that obtain values from future values of other columns

Hi guys!

I have a data set containing of three columns named 'id', 'admdate' , and 'disdate'. I want to define another column (name 'diff') under the following conditions :

if 'id' is the same as its future value, then diff =future value of 'admdate' minus current value of 'disdate' ;   else diff = 31 ;

Here's an example of what I like to do:

idadmdatedisdatediff
1471
18105
1151631
29132
2152931
37127
319232
3253031
4182431
521264
5303131

the data has already sorted by 'id'. Any helpful comments would be grateful.

Issac

Accepted Solutions
Solution
‎10-17-2012 11:29 AM
PROC Star
Posts: 8,163

Re: Function that obtain values from future values of other columns

Here is one possibility:

data want (drop=_;

set have;

by id;

set have ( firstobs = 2 keep = admdate rename = (admdate = _admdate2) )

have (      obs = 1 drop = _all_                        );

diff=ifn(missing(_admdate2) or last.id,31,_admdate2-disdate);

run;

All Replies
Solution
‎10-17-2012 11:29 AM
PROC Star
Posts: 8,163

Re: Function that obtain values from future values of other columns

Here is one possibility:

data want (drop=_;

set have;

by id;

set have ( firstobs = 2 keep = admdate rename = (admdate = _admdate2) )

have (      obs = 1 drop = _all_                        );

diff=ifn(missing(_admdate2) or last.id,31,_admdate2-disdate);

run;

Super Contributor
Posts: 1,636

Re: Function that obtain values from future values of other columns

try:

data have;
input id a d;
cards;
1 4 7
1 8 10
1 15 16
2 9 13
2 15 29
;
data want(drop=_;
merge have(in=in1)have(firstobs=2 rename=(id=_id a=_a) drop=d);
diff=ifn(id=_id,_a-d,31);
if in1;
run;
proc print;run;
Obs    id     a     d    diff

1      1     4     7      1
2      1     8    10      5
3      1    15    16     31
4      2     9    13      2
5      2    15    29     31

Contributor
Posts: 65

Re: Function that obtain values from future values of other columns

Thanks both of you guys! It's all correct.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 3 replies
• 234 views
• 3 likes
• 3 in conversation