topic Re: Attempting to different first row of derived column in SAS Programming

Attempting to different first row of derived column

Bounce — Thu, 12 Sep 2019 01:33:26 GMT

Hi all,

I am still quite new with SAS and I am currently trying to derive a new column in which each row is pretty much the increment that the other row increased by. So for instance, if I have data 'X', I am trying to derive column 'Y':

X Y
100 100

350 250

760 410

900 140

1200 300

2000 800

I am currently using this code:

data want;

set have;

Y=dif(X);

by /*insert personal sorted hierarchy*/ (first.ID,.,dif(X));

run;

Since I am trying to derive this column for different IDs, I order them to have the increments change with each new ID etc. This works well, however the problem I get is that the very first derived row is a blank for each new ID. However I would like to have the first row of Y to be the same as X shown above instead of a blank.

If anyone could please assist me, that would be great!

Thanks in advance.

Re: Attempting to different first row of derived column

novinosrin — Thu, 12 Sep 2019 01:52:51 GMT

/*No ID var*/
data want;
set have;
y=ifn(_n_=1,x,dif(x));
run;
/*for ID var*/
data want;
set have;
by id;
y=ifn(first.id,x,dif(x));
run;

Re: Attempting to different first row of derived column

maguiremq — Thu, 12 Sep 2019 02:00:35 GMT

A less elegant way than above:

data have;
input x;
datalines;
100
350
760
900
1200
2000
;
run;

data want (drop=y rename=(y2=y));
set have;
y = x;
y2 = x-lag(x);
if y2 = . then y2 = y;
run;