Function that obtain values from future values of other columns

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

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: 7,363

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

Here is one possibility:

data want (drop=_Smiley Happy;

  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;

View solution in original post


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

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

Here is one possibility:

data want (drop=_Smiley Happy;

  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=_Smiley Happy;
  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.

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

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