turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- new column in dataset

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-02-2009 03:07 PM

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-02-2009 03:37 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-02-2009 03:59 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-02-2009 04:45 PM

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

Regards,

Linus

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

04-02-2009 04:57 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-02-2009 04:45 PM

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.

=====

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-03-2009 06:18 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-03-2009 08:34 AM

Hi guys!

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

Lidia

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

Lidia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-03-2009 10:49 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to genkiboy

04-06-2009 11:13 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lidia

04-06-2009 12:42 PM

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

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-06-2009 02:44 PM

Thank you Scott! I found it.