add missing records to the dataset

[michtka]

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.

[mfab]

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

[michtka]

Genius!

Thanks.

[data_null__ · Accepted solution]

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;

[cwilson]

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

Thanks!

[cwilson]

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 ;

[michtka]

Thanks cwilson, nice one.

[art297]

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

;

data to_add;

  input visit order var;

  datalines;

2 1 0

4 1 0

;

data want;

  set have to_add;

run;

[data_null__]

Arthur Tabachneck wrote:

Why not just add the records manually?

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

[cwilson]

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.

[art297]

: 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.

[cwilson]

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.

[michtka]

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.