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!
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
data want;
set have;
by companyid;
retain outcome;
if score ne . then outcome=score;
run;
cud u kindly read my thingy once more? that doesnt work. i want to put 1993 values in 1994 and 1995, for example
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;
thank you! have a good night
Add one statement:
if first.companyid then call missing(outcome);
Would be complete.
Regards,
Haikuo
it is not working for some strange reason.
i just want to arrive at outcome column using the 1st 3 columns.
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
wow u r a magician haikuo!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.