topic Re: add missing records to the dataset in SAS Procedures

add missing records to the dataset

michtka — Thu, 03 Jan 2013 11:34:34 GMT

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

Re: add missing records to the dataset

mfab — Thu, 03 Jan 2013 14:31:59 GMT

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

Re: add missing records to the dataset

data_null__ — Thu, 03 Jan 2013 14:35:35 GMT

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;

Re: add missing records to the dataset

cwilson — Thu, 03 Jan 2013 14:38:35 GMT

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 ;

else output ;

drop i ;

run ;

* now resort dataset ;

proc sort data = have_checked ; by visit order ;

run ;

Re: add missing records to the dataset

art297 — Thu, 03 Jan 2013 14:40:21 GMT

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;

Re: add missing records to the dataset

cwilson — Thu, 03 Jan 2013 14:44:14 GMT

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

Thanks!

Re: add missing records to the dataset

data_null__ — Thu, 03 Jan 2013 14:50:32 GMT

Arthur Tabachneck wrote:

Why not just add the records manually?

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

Re: add missing records to the dataset

cwilson — Thu, 03 Jan 2013 14:55:26 GMT

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.

Re: add missing records to the dataset

michtka — Thu, 03 Jan 2013 15:24:33 GMT

Genius!

Thanks.

Re: add missing records to the dataset

michtka — Thu, 03 Jan 2013 15:27:18 GMT

Thanks cwilson, nice one.

Re: add missing records to the dataset

art297 — Thu, 03 Jan 2013 15:54:08 GMT

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

Re: add missing records to the dataset

cwilson — Thu, 03 Jan 2013 16:04:42 GMT

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.

Re: add missing records to the dataset

michtka — Thu, 03 Jan 2013 17:19:42 GMT

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.