topic Re: Increment row based on change in record in SAS Programming

Increment row based on change in record

omega1983 — Fri, 26 Jul 2013 14:04:12 GMT

data task;

set task2;

obs = _N_;

run;

This will assign the variable OBS and increment each row. Let's say that if a loan number is listed twice (say row 12 and row 13) I want to increment once or show both as the 12th record. I want an increment based on the change in a loan number

Loan_Number OBS

111222 1

112333 2

231111 3

143111 4

333333 5

How could I modify the code to accomplish this

Re: Increment row based on change in record

Keith — Fri, 26 Jul 2013 14:27:12 GMT

data have;

input Loan_Number;

datalines;

111222

112333

231111

143111

333333

;

run;

proc sort data=have;

by loan_number;

run;

data want;

set have;

by loan_number;

obs+first.loan_number;

run;

or if you want to preserve the original order of the data :

data want;

set have;

by loan_number notsorted;

obs+first.loan_number;

run;

Re: Increment row based on change in record

Reeza — Fri, 26 Jul 2013 15:00:20 GMT

SAS FAQ: How can I create an enumeration variable by groups?

Re: Increment row based on change in record

jakarman — Fri, 26 Jul 2013 15:03:18 GMT

One possible approach is:

SAS(R) 9.4 Statements: Reference (by processing)

The notsorted is indicating it should be grouped. Order is not relevant.

first.-- and last.-- processing can give you control over the group begin/end.
The retain statement (and lag) over values of previous record as ordered in the dataset.

Be carefull to use obs as name, it also used as an automatic variabele with same value as _n_.

Re: Increment row based on change in record

Jagadishkatam — Fri, 26 Jul 2013 15:24:08 GMT

Hi,

Here is anothyer way to get the desired results.

data sample;

input loan_number;

cards;

111222

112333

231111

143111

333333

;

run;

proc sql;

create table new as select loan_number,monotonic() as obs from (select distinct loan_number from sample);

select a.loan_number,b.obs from sample a left join new b on a.loan_number=b.loan_number order by obs;

quit;

Hope it is helpful.

Thanks,

Jagadish

Re: Increment row based on change in record

Patrick — Sat, 27 Jul 2013 03:51:51 GMT

or if your loan_numbers are not sorted at all here another way to go without the need to sort anywhere.

data sample(drop=_:);

input loan_number;

if _n_=1 then

do;

length loan_id 8;

declare hash h1();

_rc=h1.defineKey('loan_number');

_rc=h1.defineData('loan_id');

_rc=h1.defineDone();

end;

if h1.find() ne 0 then

do;

loan_id=_n_;

_rc=h1.add();

end;

cards;

111222

112333

231111

143111

333333

112333

;

run;