DATA Step, Macro, Functions and more

Increment row based on change in record

Reply
Contributor
Posts: 59

Increment row based on change in record

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

112333                                            2

231111                                            3

143111                                            4

333333                                            5

333333                                            5

How could I modify the code to accomplish this

Regular Contributor
Posts: 151

Re: Increment row based on change in record

Posted in reply to omega1983

data have;

input Loan_Number;

datalines;

111222

112333

112333

231111

143111

333333

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;

Super User
Posts: 19,789

Re: Increment row based on change in record

Posted in reply to omega1983
Trusted Advisor
Posts: 3,212

Re: Increment row based on change in record

Posted in reply to omega1983

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_.      

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,137

Re: Increment row based on change in record

Posted in reply to omega1983

Hi,

Here is anothyer way to get the desired results.

data sample;

    input loan_number;

cards;

111222                                         

112333                                         

112333                                         

231111                                         

143111                                         

333333                                         

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

Thanks,
Jag
Respected Advisor
Posts: 4,173

Re: Increment row based on change in record

Posted in reply to omega1983

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

data sample(drop=_Smiley Happy;

    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                                        

112333                                        

231111                                        

143111                                        

333333                                        

333333

112333                                        

;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 403 views
  • 0 likes
  • 6 in conversation