BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hua
Obsidian | Level 7 hua
Obsidian | Level 7

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! 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
Ksharp
Super User
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
hua
Obsidian | Level 7 hua
Obsidian | Level 7

Thanks very much! It works very well.

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

 

Ksharp
Super User

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

hua
Obsidian | Level 7 hua
Obsidian | Level 7

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

Spoiler
 
Ksharp
Super User
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.

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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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