DATA Step, Macro, Functions and more

Filling in gaps in sequential data

Reply
Occasional Contributor
Posts: 17

Filling in gaps in sequential data

SAS-L'ers
I have an interesting data step problem which should be easy, but whose solution is escaping me. Essentially, I have a data set with gaps in it :

QNUM Rating
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
1 9.8
2 7.0
3 6.3
4 4.9
5 5.5
7 6.3
8 8.9
9 9.9
10 7.9
etc.
There are ten questions total. What I'm looking for is code to 'fill in the gaps' with ratings set to missing :
QNUM Rating
1 3.0
2 2.9
3 10.0
4 . <-- filled in missing Q4
5 7.7
6 8.9
7 9.1
8 . <-- filled in missing Q8
9 10.0
10 7.6

I've tried all kinds of counters and flags/DO loops, etc. to no avail. Anybody have any hints ?

Barry Walton
Super Contributor
Super Contributor
Posts: 365

Re: Filling in gaps in sequential data

Posted in reply to enginemane44
Hello Enginemane44,

It looks like your input dataset should contain an additional variable, say ID, to distinguish two sets of data. Anyway, to solve the problem for the first set of questions you can use the following code:

data a;
input QNUM Rating;
datalines;
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
;
run;
data t;
do qnum=1 to 10;
output;
end;
run;
data r;
merge a(in=a) t(in=t);
if a and t then output;
if not a and t then output;
by qnum;
run;

Sincerely,
Valued Guide
Posts: 634

Re: Filling in gaps in sequential data

If the data are not strictly sequential or if the list of possible QNUMs is not well specified, a double transpose using QNUM as the ID variable will also fill in the gaps.
Super User
Posts: 10,020

Re: Filling in gaps in sequential data

Posted in reply to enginemane44
Hi.I think Patrick can do it very well.
[pre]
data temp;
input num rate;;
cards;
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
1 9.8
2 7.0
3 6.3
4 4.9
5 5.5
7 6.3
8 8.9
9 9.9
10 7.9
;
run;
data result(drop=num rate);
set temp;
qnum+1;
do while(qnum lt num) ;
call missing(rating);
output;
qnum+1;
end;
rating=rate;
output;
if qnum eq 10 then qnum=0;
run;
proc print noobs;run;

[/pre]
Ksharp
Occasional Contributor
Posts: 17

Re: Filling in gaps in sequential data

Posted in reply to enginemane44
Hi all,
Ksharp was right on - I tried the code and it worked perfectly. Thanks to Ksharp and all the others who responded. I suspected a merge may be an alternate way to do it, but I wasn't sure what to merge the data with.
Barry Walton
Ask a Question
Discussion stats
  • 4 replies
  • 555 views
  • 0 likes
  • 4 in conversation