topic Re: Create a monotonic value from a given value in SAS Programming

Create a monotonic value from a given value

ger15xxhcker — Tue, 02 Jul 2019 10:36:47 GMT

Hi all!

I would like to continue numbering from a fixed number. There is a table in one id eg: 101 . And there is another table where this id is not filled and I want to continue the numbering like a monotonic number in this field.

I hope I have made it clear example.

Thanks for all.

Re: Create a monotonic value from a given value

PeterClemmensen — Tue, 02 Jul 2019 10:39:53 GMT

Just to get things started, lets assume your data looks like this

data Table1;
input id var;
datalines;
1 10
2 20
3 30
4 40
5 50
;

data Table2;
input id var;
datalines;
. 10
. 20
. 30
. 40
. 50
;

From what I understand, you want the Id values in Table2 to be filled with values 6, 7, 8, 9 and 10, correct?

Re: Create a monotonic value from a given value

ger15xxhcker — Tue, 02 Jul 2019 10:46:53 GMT

Exactly!

Re: Create a monotonic value from a given value

PeterClemmensen — Tue, 02 Jul 2019 11:06:45 GMT

This approach does not require the data to be sorted

data Table1;
input id var;
datalines;
3 30
2 20
5 50
4 40
1 10
;

data Table2;
input id var;
datalines;
. 10
. 20
. 30
. 40
. 50
;

data want(drop=_:);
    do until (lr1);
        set Table1 end=lr1;
        if id gt _id then _id=id;
    end;
    do _N_=1 by 1 until (lr2);
        set Table2 end=lr2;
        id=_id+_N_;
        output;
    end;
run;

Re: Create a monotonic value from a given value

Patrick — Tue, 02 Jul 2019 12:02:34 GMT

@ger15xxhcker

Below should work as well:

proc sql noprint;
  select max(id) into :max_id trimmed
  from table1
  ;
quit;

data table2_want;
  set table2;
  id=sum(&max_id,_n_);
run;

Re: Create a monotonic value from a given value

ger15xxhcker — Tue, 02 Jul 2019 12:05:57 GMT

Thank you!