## add missing records to the dataset

Solved
Super Contributor
Posts: 301

# add missing records to the dataset

Hi everyone, and happy new year!

They are some ways to add the records var=0 in visit=2 and visit=4 when order=1 is missing to my have dataset?

data have;

input visit order var;

datalines;

0 1 1

0 2 3

1 1 3

1 2 4

2 2 2

3 1 4

3 2 5

4 2 6

;

run;

Cheers

V.

Accepted Solutions
Solution
‎01-03-2013 09:35 AM
Posts: 3,852

## Re: add missing records to the dataset

The value of VAR will be missing.....

data have;
input visit order var;
datalines;
0 1 1
0 2 3
1 1 3
1 2 4
2 2 2
3 1 4
3 2 5
4 2 6
;

run;
proc summary data=have completetypes nway;

class visit order;
output out=test idgroup(out(var)=);
run;

All Replies
Frequent Contributor
Posts: 131

## Re: add missing records to the dataset

Hi michtka,

Happy new year for you, as well! :-)

I am having trouble understanding your problem. Could you perhaps please try to rephrase the question? Or could you maybe post us your desired output dataset?

I am sure there is a solution to your problem, I just haven't fully understood it yet.

Cheers,

Michael

Super Contributor
Posts: 301

## Re: add missing records to the dataset

Genius!

Thanks.

Solution
‎01-03-2013 09:35 AM
Posts: 3,852

## Re: add missing records to the dataset

The value of VAR will be missing.....

data have;
input visit order var;
datalines;
0 1 1
0 2 3
1 1 3
1 2 4
2 2 2
3 1 4
3 2 5
4 2 6
;

run;
proc summary data=have completetypes nway;

class visit order;
output out=test idgroup(out(var)=);
run;
Contributor
Posts: 43

## Re: add missing records to the dataset

Ah!  data_null_ answer is much more clever!  I learned something new today!

Thanks!

Contributor
Posts: 43

## Re: add missing records to the dataset

I would think that you can't do it in the same data step.  You need to first sort the data, then you can use first. processing.

proc sort data = have ; by visit order ;

run ;

data have_checked ;

set have ;

by visit order ;

if first.visit and order > 1 then do ;

* output original row ;

output ;

* now create missing order rows ;

do i = 1 to order-1 ;

order = i ;

var = 0 ;  *corrected to set var = 0, as specified in the question;

output ;

end ;

end ;

else output ;

drop i ;

run ;

* now resort dataset ;

proc sort data = have_checked ; by visit order ;

run ;

Super Contributor
Posts: 301

## Re: add missing records to the dataset

Thanks cwilson, nice one.

PROC Star
Posts: 8,163

## Re: add missing records to the dataset

Using DN's method will produce missing for the new var 0s.  Why not just add the records manually?  e.g.:

data have;

input visit order var;

datalines;

0 1 1

0 2 3

1 1 3

1 2 4

2 2 2

3 1 4

3 2 5

4 2 6

;

input visit order var;

datalines;

2 1 0

4 1 0

;

data want;

run;

Posts: 3,852

## Re: add missing records to the dataset

```Arthur Tabachneck wrote:

Why not just add the records manually?

```

What if you don't know which ones are missing?

Contributor
Posts: 43

## Re: add missing records to the dataset

I do not recommend a manual data step.  We want a solution that can be used on a much larger dataset, without previous knowledge of the missing rows.  We want a solution that the computer can do for us.

PROC Star
Posts: 8,163

## Re: add missing records to the dataset

: Aside from learning DN's always fascinating approaches to various problems, there is another lesson to be learned here: the more clearly one can state the problem they are trying to solve, the more likely they will obtain the best solution.

You mentioned "We want a solution ..", but the thread was posted by .  Regardless, the specs were for:

"add the records var=0 in visit=2 and visit=4 when order=1"

That apparently was not what was actually wanted but it is what was requested.

Contributor
Posts: 43

## Re: add missing records to the dataset

Point taken.  When I said "We want", perhaps what I should have said was "One should strive for".  Furthermore, I "assumed" that the example data was merely for posting, and that a solution for a larger, "real world" dataset was needed.

Super Contributor
Posts: 301

## Re: add missing records to the dataset

Yes, Carla Wilson your inference was right, my real problem was a bigger data set.

My apologize Sr. Arthur for being simplistic in my post.

Anyway, Thanks for your help guys.

🔒 This topic is solved and locked.