DATA Step, Macro, Functions and more

new column in dataset

Reply
Occasional Contributor
Posts: 6

new column in dataset

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!
Frequent Contributor
Frequent Contributor
Posts: 77

Re: new column in dataset

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;
Occasional Contributor
Posts: 6

Re: new column in dataset

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.
Super User
Posts: 5,441

Re: new column in dataset

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
Frequent Contributor
Posts: 102

Re: new column in dataset

I don't see a need for the two sorts in LAP's code.
Frequent Contributor
Posts: 102

Re: new column in dataset

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.
Super Contributor
Posts: 474

Re: new column in dataset

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
Occasional Contributor
Posts: 6

Re: new column in dataset

Hi guys!

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

Lidia
Occasional Contributor
Posts: 5

Re: new column in dataset

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
Occasional Contributor
Posts: 6

Re: new column in dataset

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: new column in dataset

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

Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 6

Re: new column in dataset

Thank you Scott! I found it.
Ask a Question
Discussion stats
  • 11 replies
  • 163 views
  • 0 likes
  • 7 in conversation