new column in dataset

[Lidia]

I need to create new column in my data set. All values in it should be shifted up by 1 cell in comparison with other column.

Let say I have:
a b
1 23
2 15
3 45
4 55
5 67
I need to add the column c such as:
a b c
1 23 15
2 15 45
3 45 55
4 55 67
5 67 0

What I should do in this case?

Thanks a lot!

[LAP]

It appears as if your a variable defines the order of the observations. If this is the case I would sort the data in descending order of A and then use the lag function in a data step to lag the first obs to the second, second to third etc.

If A does not define the order of the observations I would probably create a variable based on the obs number in a data step first (use the _n_ variable)

Let me know if you need further explantion

Linda

Data one;
input a b;
obsnum= _n_;
run;

proc sort ; by descending obsnum;

data two;
set one;
newcol = lag(b);
run;

proc sort data = two; by obsnum; * this gets in back in the original order;

[Lidia]

Thank you for your answer but I do not think this is the case.

I want to have something like this:

for i=1 to 4;
do:
c{i}=b{i+1};
end;
c{5}=0 (or missing).

or something like this:

if a=1 then c=b where a=2;
and so on..

I do not know haw to do this based on the existing dataset.

In my dataset I have this example repeated many times. So var.a is not an observation number.

Thanks.

[LinusH]

Your code in the last post does not really make sense... My interpretation of your question is that LAP solved it for you. Since have no variable which naturally holds the order, it is created temporarily (obsnum). Please read his/hers post again more carefully.

Regards,
Linus

[JackHamilton]

I don't see a need for the two sorts in LAP's code.

[JackHamilton]

Here's one way:

=====
data test;
input a b;
cards4;
1 23
2 15
3 45
4 55
5 67
;;;;

options mergenoby=nowarn;
data new;
retain a b c;
merge test
test (keep=b rename=(b=c) firstobs=2)
end=eod;
if eod then
c = 0;
run;
options mergenoby=error;

proc print;
run;
=====

Sorry about the lack of indentation - the forum software doesn't preserve it.

[DanielSantos]

Hi guys.

Here's another solution.

If your not concerned about the final order of the rows, try this:

data OUT;
set IN (firstobs=2) end=_EOF ; /* first set starts at 2nd row */
if _EOF then do; /* deals with last row */
C=0;
output;
end;
C=B; /* stores value of B into C */
set IN; /* second set starts at 1st row */
output;
run;

this results in:

a b c
1 23 15
2 15 45
3 45 55
5 67 0
4 55 67

result is as expected, but the last row positions is switched with the previous.

Greetings from Portugal.

Daniel Santos at www.cgd.pt

[Lidia]

Hi guys!

Thank you all for your advise. You gave me some idea haw to deal with it.

Lidia

[genkiboy]

The following will do it:

data test;
input a b;
cards;
1 23
2 15
3 45
4 55
5 67
;;;;

data lastzero;
input a b;
cards;
0 0
;;;;

data test2;
set test lastzero;
aprime=lag(a);
bprime=lag(b);
cprime=b;

if aprime ne . ;

drop a b;
rename aprime=a bprime=b cprime=c;
run;

Kevin

[Lidia]

Hi,
Again. Thanks a lot. I used this lag() function. I did not now that this function exists before. I tried to search “help” for some function to do my task. I used wrong keywords all the times. With this function it took me 5 sec. to calculate what I need.

Now I am wandering if it is possible to find a list of all functions SAS can use? Well , of course there are thousands of them but one can scroll down thought the list and find the appropriate function he/she needs.

[sbb]

The SAS system's "Help and Documentation" (F1 key) has a keyword reference for "functions, by category".

Scott Barry
SBBWorks, Inc.

[Lidia]

Thank you Scott! I found it.