topic Re: From a long table with multiple missing to one row without missing in SAS Programming

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

Dinurik — Wed, 30 Sep 2020 20:59:49 GMT

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!

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

Reeza — Wed, 30 Sep 2020 21:26:07 GMT

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!

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

Dinurik — Wed, 30 Sep 2020 21:32:59 GMT

Hi Reeza,

Thanks! SAS returned this error message:

ERROR: UPDATE statement needs a BY statement.

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

ballardw — Wed, 30 Sep 2020 22:18:24 GMT

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

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

novinosrin — Wed, 30 Sep 2020 23:21:52 GMT

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;

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

Dinurik — Wed, 30 Sep 2020 23:35:50 GMT

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!