Programming the statistical procedures from SAS

How to calculate the number of continuous missing values

Accepted Solution Solved
Reply
Contributor hua
Contributor
Posts: 36
Accepted Solution

How to calculate the number of continuous missing values

I have a dataset like following, and I want to use proc expand to interpolate the missing value if the number of continuous missing is less than 6. So  I need to calculate the number of continuous missing values, and add a new variable as the number of continuous missing values to the dataset. I have 2000 stations and 30000 obs for each station, so a macro or a loop may be used to calculate.

 

input dataset:

station  year  month  day   var

1          2015     1        1      54

1          2015     1        2      .

1          2015     1        3      32

1          2015     1        4      48

1          2015     1        5      52

1          2015     1        6      .

1          2015     1        7      .

1          2015     1        8      .

1          2015     1        9      49

1          2015     1       10     50

2          2015     1        1      .

2          2015     1        2      53

2          2015     1        3      .

2          2015     1        4      .

2          2015     1        5      55

2          2015     1        6      .

2          2015     1        7      .

2          2015     1        8      .

2          2015     1        9      47

2          2015     1       10     58

 

and I want to get:

station  year  month  day   var   missing

1          2015     1        1      54       0

1          2015     1        2      .          1

1          2015     1        3      32       0

1          2015     1        4      48       0

1          2015     1        5      52       0

1          2015     1        6      .          3

1          2015     1        7      .          3

1          2015     1        8      .          3

1          2015     1        9      49       0

1          2015     1       10     50       0

2          2015     1        1      .          1

2          2015     1        2      53       0

2          2015     1        3      .          2

2          2015     1        4      .          2

2          2015     1        5      55       0

2          2015     1        6      .          3

2          2015     1        7      .          3

2          2015     1        8      .          3

2          2015     1        9      47       0

2          2015     1       10     58       0

 

Thank you for help! Smiley Happy

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 9,779

Re: How to calculate the number of continuous missing values

Try this one :
data have;
input inputstation  year  month  day   var;
cards;
1          2015     1        1      54
1          2015     1        2      .
1          2015     1        3      32
1          2015     1        4      48
1          2015     1        5      52
1          2015     1        6      .
1          2015     1        7      .
1          2015     1        8      .
1          2015     1        9      49
1          2015     1       10     50
2          2015     1        1      .
2          2015     1        2      53
2          2015     1        3      .
2          2015     1        4      .
2          2015     1        5      55
2          2015     1        6      .
2          2015     1        7      .
2          2015     1        8      .
2          2015     1        9      47
2          2015     1       10     58
;
run;
data want;
count=0;
do until(last.var);
 set have;
 by inputstation  var notsorted;
 if missing(var) then count+1;
end;
do until(last.var);
set have;
 by inputstation  var notsorted;
 output;
end;
run;
 
Xia Keshan

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 9,779

Re: How to calculate the number of continuous missing values

Try this one :
data have;
input inputstation  year  month  day   var;
cards;
1          2015     1        1      54
1          2015     1        2      .
1          2015     1        3      32
1          2015     1        4      48
1          2015     1        5      52
1          2015     1        6      .
1          2015     1        7      .
1          2015     1        8      .
1          2015     1        9      49
1          2015     1       10     50
2          2015     1        1      .
2          2015     1        2      53
2          2015     1        3      .
2          2015     1        4      .
2          2015     1        5      55
2          2015     1        6      .
2          2015     1        7      .
2          2015     1        8      .
2          2015     1        9      47
2          2015     1       10     58
;
run;
data want;
count=0;
do until(last.var);
 set have;
 by inputstation  var notsorted;
 if missing(var) then count+1;
end;
do until(last.var);
set have;
 by inputstation  var notsorted;
 output;
end;
run;
 
Xia Keshan
Contributor hua
Contributor
Posts: 36

Re: How to calculate the number of continuous missing values

Thanks very much! It works very well.

what does "var notsorted" in " by station  var notsorted;" means?  

 

Super User
Posts: 9,779

Re: How to calculate the number of continuous missing values

[ Edited ]

It take every side by side as a group. E.X.
                        Group
1 2015 1 4 48   1
1 2015 1 5 52   2
1 2015 1 6 .      3
1 2015 1 7 .      3
1 2015 1 8 52   4

Contributor hua
Contributor
Posts: 36

Re: How to calculate the number of continuous missing values

And do you know how to use if statement in proc expand process?

Spoiler
 
Super User
Posts: 9,779

Re: How to calculate the number of continuous missing values

NO. I don't think you can use IF in proc expand.
But you can firstly use proc expand and padding these missing value, then use IF in data step to process what you want.
🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 495 views
  • 3 likes
  • 2 in conversation