topic Re: new column in dataset in SAS Programming

new column in dataset

Lidia — Thu, 02 Apr 2009 19:07:44 GMT

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!

Re: new column in dataset

LAP — Thu, 02 Apr 2009 19:37:02 GMT

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;

Re: new column in dataset

Lidia — Thu, 02 Apr 2009 19:59:10 GMT

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.

Re: new column in dataset

JackHamilton — Thu, 02 Apr 2009 20:45:09 GMT

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.

Re: new column in dataset

LinusH — Thu, 02 Apr 2009 20:45:42 GMT

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

Re: new column in dataset

JackHamilton — Thu, 02 Apr 2009 20:57:06 GMT

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

Re: new column in dataset

DanielSantos — Fri, 03 Apr 2009 10:18:33 GMT

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

Re: new column in dataset

Lidia — Fri, 03 Apr 2009 12:34:10 GMT

Hi guys!

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

Lidia

Re: new column in dataset

genkiboy — Fri, 03 Apr 2009 14:49:13 GMT

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

Re: new column in dataset

Lidia — Mon, 06 Apr 2009 15:13:20 GMT

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.

Re: new column in dataset

sbb — Mon, 06 Apr 2009 16:42:05 GMT

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

Scott Barry
SBBWorks, Inc.

Re: new column in dataset

Lidia — Mon, 06 Apr 2009 18:44:54 GMT

Thank you Scott! I found it.