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.
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;
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.
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;
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;
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;
@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!!!
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.