replacing question

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

replacing question

company idyearscoreoutcome
11990..
11991..
11992..
119931212
11994.12
219901515
21991.15
21992.15
219931717
21994.17
219951919
21996.19
319901515
31991.15
31992.15
319931515
31994.15
319951616
31996.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!


Accepted Solutions
Solution
‎10-28-2014 05:09 PM
Respected Advisor
Posts: 3,156

Re: replacing question

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,229

Re: replacing question

data want;

set have;

by companyid;

retain outcome;

if score ne . then outcome=score;

run;

Frequent Contributor
Posts: 102

Re: replacing question

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

Trusted Advisor
Posts: 1,229

Re: replacing question

I've added one more statement as suggested by . 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;

Frequent Contributor
Posts: 102

Re: replacing question

thank you! have a good night

Respected Advisor
Posts: 3,156

Re: replacing question

Add one statement:

if first.companyid then call missing(outcome);

Would be complete.

Regards,

Haikuo

Frequent Contributor
Posts: 102

Re: replacing question

it is not working for some strange reason.

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

Solution
‎10-28-2014 05:09 PM
Respected Advisor
Posts: 3,156

Re: replacing question

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

Frequent Contributor
Posts: 102

Re: replacing question

wow u r a magician haikuo!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 238 views
  • 3 likes
  • 3 in conversation