topic Re: replacing question in SAS Programming

replacing question

aarony — Tue, 28 Oct 2014 20:46:13 GMT

company id	year	score	outcome
1	1990	.	.
1	1991	.	.
1	1992	.	.
1	1993	12	12
1	1994	.	12
2	1990	15	15
2	1991	.	15
2	1992	.	15
2	1993	17	17
2	1994	.	17
2	1995	19	19
2	1996	.	19
3	1990	15	15
3	1991	.	15
3	1992	.	15
3	1993	15	15
3	1994	.	15
3	1995	16	16
3	1996	.	16

Hi i have a dataset. column 1-3 that has company id, year, and score. i want to arrive at column4.

column 3 only has 1990, 1993, 1995 data. i want to replace 1991, 1992 outcome with 1990 score. i want to replace 1994 outcome with 1993 score. i want to replace 1996 outcome with 1995 score.

i think i have to use replace but cud u please help me?

thank you!

Re: replacing question

stat_sas — Tue, 28 Oct 2014 20:56:26 GMT

data want;

set have;

by companyid;

retain outcome;

if score ne . then outcome=score;

run;

Re: replacing question

aarony — Tue, 28 Oct 2014 21:01:21 GMT

cud u kindly read my thingy once more? that doesnt work. i want to put 1993 values in 1994 and 1995, for example

Re: replacing question

Haikuo — Tue, 28 Oct 2014 21:04:00 GMT

Add one statement:

if first.companyid then call missing(outcome);

Would be complete.

Regards,

Haikuo

Re: replacing question

aarony — Tue, 28 Oct 2014 21:06:13 GMT

it is not working for some strange reason.

i just want to arrive at outcome column using the 1st 3 columns.

Re: replacing question

Haikuo — Tue, 28 Oct 2014 21:09:15 GMT

Works for me:

data have;

infile cards dlm='09'x;

input company_id year score;

cards;

1 1990 .

1 1991 .

1 1992 .

1 1993 12

1 1994 .

2 1990 15

2 1991 .

2 1992 .

2 1993 17

2 1994 .

2 1995 19

2 1996 .

3 1990 .

3 1991 .

3 1992 .

3 1993 15

3 1994 .

3 1995 16

3 1996 .

;

run;

data want;

do until (last.company_id);

set have;

by company_id notsorted;

outcome=coalesce(score,outcome);

output;

end;

run;

Haikuo

Re: replacing question

aarony — Tue, 28 Oct 2014 21:11:58 GMT

wow u r a magician haikuo!

Re: replacing question

stat_sas — Tue, 28 Oct 2014 21:13:27 GMT

I've added one more statement as suggested by Hai.kuo. Tyr this, hope you will get the desired column named as outcome

data want;
set have;
by companyid;
retain outcome;
if first.companyid then call missing(outcome);
if score ne . then outcome=score;
run;

Re: replacing question

aarony — Tue, 28 Oct 2014 21:15:20 GMT

thank you! have a good night