From a long table with multiple missing to one row without missing

[Dinurik]

Hi All,

I apologize if this has been asked before, this question is tricky to formulate, let alone find an answer in the forum. 

I have a table like this:

    data have;
    input theta1 theta3 sigma11 sigma13;
    datalines;
        . . . .
        0.22 . . .
        . . . .
        . 0.29 . .
        . . . .
        . . 0.02 -0.02
        . . . .
    ;
    run;

And I need to get a table like this: all values in one row:

    data want;
    input theta1 theta3 sigma11 sigma13 ;
    datalines;
        0.22 0.29 0.02 -0.02
    ;
    run;

I am curious if this can be done in few steps. I would appreciate any help!

[Reeza]

data want;
update have(obs=0) have;
run;

Usually you'd want a BY statement as well.

---

@Dinurik wrote:

Hi All,

I apologize if this has been asked before, this question is tricky to formulate, let alone find an answer in the forum. 

I have a table like this:

    data have;
    input theta1 theta3 sigma11 sigma13;
    datalines;
        . . . .
        0.22 . . .
        . . . .
        . 0.29 . .
        . . . .
        . . 0.02 -0.02
        . . . .
    ;
    run;

And I need to get a table like this: all values in one row:

    data want;
    input theta1 theta3 sigma11 sigma13 ;
    datalines;
        0.22 0.29 0.02 -0.02
    ;
    run;

I am curious if this can be done in few steps. I would appreciate any help!

---

[Dinurik]

Hi Reeza,

Thanks! SAS returned this error message:

ERROR: UPDATE statement needs a BY statement.

[ballardw]

---

@Dinurik wrote:

Hi All,

I apologize if this has been asked before, this question is tricky to formulate, let alone find an answer in the forum. 

I have a table like this:

    data have;
    input theta1 theta3 sigma11 sigma13;
    datalines;
        . . . .
        0.22 . . .
        . . . .
        . 0.29 . .
        . . . .
        . . 0.02 -0.02
        . . . .
    ;
    run;

And I need to get a table like this: all values in one row:

    data want;
    input theta1 theta3 sigma11 sigma13 ;
    datalines;
        0.22 0.29 0.02 -0.02
    ;
    run;

I am curious if this can be done in few steps. I would appreciate any help!

---

This works for your specific example:

proc summary data=have ;
   var  theta1 theta3 sigma11 sigma13;
   output out=want (drop=_:) max=;
run;

However if you have any of the variables with more than one non-missing values then likely it won't yield what you want, and will only work for numeric values.

If you have data with more than one value for any of your variables then you need to provide an example what the output for that should look like.

[novinosrin · Accepted solution]

Hi @Dinurik  , @Reeza 's approach is neat. Hovvever,You need to create a junk BY variable in an intermediate step.

    data have;
    input theta1 theta3 sigma11 sigma13;
    datalines;
        . . . .
        0.22 . . .
        . . . .
        . 0.29 . .
        . . . .
        . . 0.02 -0.02
        . . . .
    ;
    run;

	data _have;
	 set have;
	 retain t 99999;
	 run;

data want;
 update _have(obs=0) _have;
 by t;
 drop t;
run; 

[Dinurik]

Hi @novinosrin ,

Thank you!

I created a junk id variable through id=_n_, and it didn't occur to me that id is not supposed to change for the update statement to work. Thank you so much for the explanations!