BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aarony
Obsidian | Level 7
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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

8 REPLIES 8
stat_sas
Ammonite | Level 13

data want;

set have;

by companyid;

retain outcome;

if score ne . then outcome=score;

run;

aarony
Obsidian | Level 7

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

stat_sas
Ammonite | Level 13

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;

aarony
Obsidian | Level 7

thank you! have a good night

Haikuo
Onyx | Level 15

Add one statement:

if first.companyid then call missing(outcome);

Would be complete.

Regards,

Haikuo

aarony
Obsidian | Level 7

it is not working for some strange reason.

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

Haikuo
Onyx | Level 15

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

aarony
Obsidian | Level 7

wow u r a magician haikuo!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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