Help using Base SAS procedures

add missing records to the dataset

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

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
Respected Advisor
Posts: 3,799

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 126

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
Respected Advisor
Posts: 3,799

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

Posted in reply to data_null__

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: 7,474

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

;

data to_add;

  input visit order var;

  datalines;

2 1 0

4 1 0

;

data want;

  set have to_add;

run;

Respected Advisor
Posts: 3,799

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: 7,474

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 554 views
  • 4 likes
  • 5 in conversation