DATA Step, Macro, Functions and more

create a ascending number conditionally in the last column

Reply
Frequent Contributor
Posts: 133

create a ascending number conditionally in the last column


WANT TO assign _n_ number in ascending order based on sec, dt, rate in the last column.


data t;

input sec dt rate;

cards;

1 9205 0.2

1 9205 0.3

1 9205 0.4

1 9204 0.2

1 9204 0.3

1 9204 0.4

2 9205 0.1

2 9205 0.2

2 9204 0.1

2 9204 0.2

3 9205 0.8

3 9205 0.9

3 9205 1.1

3 9205 3.5

3 9205 4.2

;

/*

WANT TO assign _n_ number in ascending order based on rate in the last column

1 9205 0.2 1

1 9205 0.3 2

1 9205 0.4 3

1 9204 0.2 1

1 9204 0.3 2

1 9204 0.4 3

2 9205 0.1 1

2 9205 0.2 2

2 9204 0.1 1

2 9204 0.2 2

3 9205 0.8 1

3 9205 0.9 2

3 9205 1.1 3

3 9205 3.5 4

3 9205 4.2 5

*/

Super User
Posts: 19,772

Re: create a ascending number conditionally in the last column

data want;

set t;

retain count;

prev_value=lag(rate);

if value<prev_value then count=1;

else count+1;

run;

Frequent Contributor
Posts: 133

Re: create a ascending number conditionally in the last column

proc sort data=t;

by sec dt;

run;

data t;

set t;

by sec dt;

if first.rate then  n=1 ;

else n+1;

if last.rate then stop;

run;


It doesn't work, I don't know how to reset the n using first. and last. form to assign the n in ascending order.

Super User
Posts: 19,772

Re: create a ascending number conditionally in the last column

Change your first.rate to first.dt

And you don't need the last. and stop line.

Contributor
Posts: 45

Re: create a ascending number conditionally in the last column

I agree with Reeza.

And if you need the kind of output you specified (group processing only, no sorting),

then you may want to add: NOTSORTED and skip pre-sorting altogether.

data t;

set t;

by sec dt NOTSORTED;

if first.dt then n=1;

else n+1;

run;

Ask a Question
Discussion stats
  • 4 replies
  • 240 views
  • 0 likes
  • 3 in conversation