SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Filling the missing value

Accepted Solution Solved
Reply
Regular Contributor
Posts: 217
Accepted Solution

Filling the missing value

Dear everyone,

 

Can I please ask how can I produce the desired output data (B) from input data (A)?

 

Your help is much appreciated.

 

 

Data A

   

Data B

   

id

yr

val

 

id

yr

val

val2

1

1

11

 

1

1

11

11

1

2

.

 

1

2

.

12

1

3

.

 

1

3

.

13

1

4

44

 

1

4

44

44

2

1

.

 

2

1

.

21

2

2

22

 

2

2

22

22

3

1

.

 

3

1

.

32

3

2

33

 

3

2

33

33

3

3

.

 

3

3

.

34

3

4

44

 

3

4

44

44

4

1

11

 

4

1

11

11

4

2

22

 

4

2

22

22

4

3

33

 

4

3

33

33

4

4

44

 

4

4

44

44

5

1

55

 

5

1

55

55

5

2

.

 

5

2

.

56

5

3

.

 

5

3

.

57

5

4

.

 

5

4

.

58

6

1

.

 

6

1

.

63

6

2

.

 

6

2

.

64

6

3

.

 

6

3

.

65

6

4

66

 

6

4

66

66


Accepted Solutions
Solution
‎01-21-2016 09:43 PM
Super User
Posts: 6,941

Re: Filling the missing value

proc sort data=have;
by id yr;
run;

data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;

proc sort data=int;
by id descending yr;
run;

data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;

proc sort data=want;
by id yr;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-21-2016 09:43 PM
Super User
Posts: 6,941

Re: Filling the missing value

proc sort data=have;
by id yr;
run;

data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;

proc sort data=int;
by id descending yr;
run;

data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;

proc sort data=want;
by id yr;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,681

Re: Filling the missing value

data have;
input id yr val;
cards;
1 1 11
1 2 . 	
1 3 .
1 4 44
2 1 . 	
2 2 22
;
run;
data temp;
 set have;
 by id;
 retain found;
 if first.id then call missing(found);
 if not missing(val) and not found then do;output;found=1;end;
run;
data want;
 merge have temp(keep=id yr val rename=(yr=_yr val=_val));
 by id;
 if missing(val) then val=_val+yr-_yr;
 drop _yr _val;
run;
Regular Contributor
Posts: 217

Re: Filling the missing value

Thank you very much KurtBremser and Kisharp.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 344 views
  • 2 likes
  • 3 in conversation