BookmarkSubscribeRSS Feed
Lidia
Calcite | Level 5
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!
11 REPLIES 11
LAP
Quartz | Level 8 LAP
Quartz | Level 8
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
Calcite | Level 5
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
Tourmaline | Level 20
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
Data never sleeps
JackHamilton
Lapis Lazuli | Level 10
I don't see a need for the two sorts in LAP's code.
JackHamilton
Lapis Lazuli | Level 10
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
Barite | Level 11
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
Calcite | Level 5
Hi guys!

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

Lidia
genkiboy
Calcite | Level 5
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
The SAS system's "Help and Documentation" (F1 key) has a keyword reference for "functions, by category".

Scott Barry
SBBWorks, Inc.
Lidia
Calcite | Level 5
Thank you Scott! I found it.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1124 views
  • 0 likes
  • 7 in conversation