Adding descending sequence number

[abdulla]

Hi, 

I want to add a descending sequence number in column ty keeping the 0 intact in the given place. Can anyone help me with sas code for this. Sheet 2 contains what I want. By the way, I have a huge number of data by many groups. So, there is no fixed value for the lowest and the highest value. 

[Reeza]

This is a simplistic way to do this. 

1. Find the start of each counter

2. Merge it with main data set

3. Count back down

---

@abdulla wrote:

Hi, 

I want to add a descending sequence number in column ty keeping the 0 intact in the given place. Can anyone help me with sas code for this. Sheet 2 contains what I want. By the way, I have a huge number of data by many groups. So, there is no fixed value for the lowest and the highest value. 

---

[PGStats · Accepted solution]

A double do until() loop will do that:

data want;
do i = 1 by 1 until(last.per);
    set have; by per;
    if ty = 0 then pos = i;
    end;
do i = -pos + 1 by 1 until(last.per);
    set have; by per;
    ty = i;
    output;
    end;
drop i pos;
run;

[abdulla]

That's really awesome. Very simple and easy solution. Thanks a lot

[kiranv_]

sql way. I just made the column name

data have;
infile datalines missover;
input id mes val;
datalines;
12 0.4
12 0.04
12 0.07
12 0.09
12 0.01
12 0.09  0
12 0.01
12 0.07
13 0.01
13 0.09
13 0.07
13 0.08
13 0.01	 0
13 0.02
13 0.03
14 0.07
14 0.06
14 0.07
14 0.09
14 0.08
14 0.09	 0
14 0.01
14 0.02
14 0.03
;

data have1/view=have1;
set have;
col =_n_;
run;

proc sql;
create table want as 
select a.id, 
      mes,
     col-ref as val
from have1 a 
left join
(select id, val, col as ref
from have1
where val = 0)b
on a.id=b.id
order by col;

[novinosrin]

While it is known prodigy genius status aka @PGStats is obvious, I felt this is a fine question to practice the concept of find,reread, replace

data have;
infile cards truncover;
input Per	trn	ty;
cards;
12	0.4	
12	0.004	
12	0.007	
12	0.012	
12	0.089	0
12	-0.32	
12	-0.657	
13	0.25	
13	0.089	
13	0.29	
13	0.37	
13	0.96	
13	-0.21	0
13	-0.128	
13	0.84	
13	0.075	
14	-0.008	
14	0.008	
14	0.089	
14	0.089	
14	0.089	
14	0.9	
14	0.5	
14	0.34	
14	-0.21	0
14	-0.12	
14	0.001	
;

data want;
set have;
by per;
retain _k _f;
if first.per then do; _k=_n_;call missing(_j,_f);end;
if ty=0 then 
	do;
		do i=_k to _n_;
			set have point=i;
			_j+1;
			ty=_j-(_n_-_k+1);
			output;
		end;
		_f=1;
	end;
else if _f then do; ty=_f;output;_f+1;end;
drop _:;
run;

[MarkWik]

@novinosrin   If for anything or anything at all you draw/will draw admiration for is your incredible understanding that is noticeable in many of your responses. The speed at which you have progressed doesn't seem like somebody learned so much so quickly. Very nice!!!

[novinosrin]

Thank you. But this is not a big problem. It's just a matter of using the case for yet another operation of CRUD(Create, Read, Update, and Delete). The idea is to figure what other/various functionality SAS provides to accomplish that. To be honest, PG's solution did click my mind too of course acknowledging my knowledge is not even a drop in PG's ocean of knowledge, however as I scrolled down and noticed that, my thoughts went to finding other ways of accomplishing the task. It's all trial and error experiments. 

So further to your nice encouraging comment, I wanted to try using hash. Again same in principle find,reread, replace.

And here it is-->

data have;
infile cards truncover;
input Per	trn	ty;
cards;
12	0.4	
12	0.004	
12	0.007	
12	0.012	
12	0.089	0
12	-0.32	
12	-0.657	
13	0.25	
13	0.089	
13	0.29	
13	0.37	
13	0.96	
13	-0.21	0
13	-0.128	
13	0.84	
13	0.075	
14	-0.008	
14	0.008	
14	0.089	
14	0.089	
14	0.089	
14	0.9	
14	0.5	
14	0.34	
14	-0.21	0
14	-0.12	
14	0.001	
;



data _null_;
if _n_=1 then do;
 dcl hash H (ordered: "A",multidata:'y') ;
   h.definekey  ("per") ;
   h.definedata ("per","trn", "ty") ;
   h.definedone () ;
end;
do _n_=1 by 1 until(last.per);
	set have end=lr;
	by per;
	if not _f then rc=h.add();
	if ty=0 then do;
		do n=1 by 1 while(h.do_over() eq 0);
		ty=n-_n_;
		RC_updt = H.replacedup();
  		end;
		_f=1;
	end;
	else if _f then do; ty=_f;rc=h.add();_f=sum(_f,1);end;
end;
if lr then h.output(dataset:'want');
run;

Results:

Per trn ty 
12 0.400 -4 
12 0.004 -3 
12 0.007 -2 
12 0.012 -1 
12 0.089 0 
12 -0.320 1 
12 -0.657 2 
13 0.250 -5 
13 0.089 -4 
13 0.290 -3 
13 0.370 -2 
13 0.960 -1 
13 -0.210 0 
13 -0.128 1 
13 0.840 2 
13 0.075 3 
14 -0.008 -8 
14 0.008 -7 
14 0.089 -6 
14 0.089 -5 
14 0.089 -4 
14 0.900 -3 
14 0.500 -2 
14 0.340 -1 
14 -0.210 0 
14 -0.120 1 
14 0.001 2