topic Re: long to wide with multiple key vars in SAS Procedures

long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 19:00:56 GMT

So here's what I have:

ID X Y Z

123 8

123 7

456 9

456 2

And what I want is this:

ID X Y Z

123 7 8

456 9 2

Help? I know retain is necessary here, right? I've been working at it all day, and I tend to run into problems when I encounter a [blank] or missing value.

Re: long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 19:08:27 GMT

Btw,

I know how to restructure. What I don't know is how to get to this interim step :

ID X Y Z

123 8

123 7 8

456 9

456 9 2

Re: long to wide with multiple key vars

Reeza — Tue, 14 Aug 2012 19:09:12 GMT

Can you have two values in a column, ie the X=3 and ID=123 below? if so what would you want to happen?

ID X Y Z

123 3 8

123 7

456 9

456 2

Otherwise, just summarize the data using a proc means or sql step to get the max/min/avg or whatever works for what you need.

proc means data=have;

class id;

var x y x;

output out=want max=;

run;

Re: long to wide with multiple key vars

ArtC — Tue, 14 Aug 2012 19:16:00 GMT

If it is assumed that the problem is as specified then the following will collapse the data.

data have;

infile datalines missover;

input id x y z;

datalines;

123 8

123 . 7

456 . 9

456 . . 2

run;

* for this specific example;

proc summary data=have nway;

class id;

var x y z;

output out=want(drop=_type_ _freq_)

sum=;

run;

proc print data=want;

run;

This solution will not work if there are other conditions such as more than one row with a non-missing value.

Re: long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 19:16:12 GMT

What I'm trying to do is to combine all the information from X, Y, and Z, into a single observation that will be kept (restructing to person level). In the original dataset, there is only one value across x, y, and z for each observation.

Re: long to wide with multiple key vars

Linlin — Tue, 14 Aug 2012 19:19:38 GMT

data have;
input ID          X          Y          Z   ;
cards;
123        .           8        .
123        7           .        .
456        .            9       .
456         .           .          2
;
options missing=' ';
data want(drop=_:);
retain _x _y _z;
set have;
by id;
if first.id then do;
   _x=x; _y=y;_z=z; end;
else do;
x=ifn(x=.,_x,x);
y=ifn(y=.,_y,y);
z=ifn(z=.,_z,z);
end;
if last.id;
run;
proc print;run;

Obs     ID    X    Y    Z

1 123 7 8
2 456 9 2

Re: long to wide with multiple key vars

mkeintz — Tue, 14 Aug 2012 19:25:31 GMT

Use the UPDATE statement, which only takes values from the right hand data set (HAVE below) if they are not missing. It writes out only one record per id (and expects only one incoming record per id in the MASTER data set). It treats the right hand data set (HAVE) as a series of "transactions" to apply to master.

In this example, even though there are 2 steps, there is only one pass through of the original dataset HAVE. That's because MASTER is a data set view, and won't be activated until it is used in a subsequent step. It's sort of a data "pipe".

The only assumption here is that HAVE is sorted by ID.

data master / view=master;

set have;

by id;

if first.id;

run;

data want;

update master have;

by id;

run;

I'd add that the benefit of this technique is that you don't need to know the names of any variables except the BY variable.

Re: long to wide with multiple key vars

Linlin — Tue, 14 Aug 2012 19:28:52 GMT

or:

proc means data=have nway ;

class id;

var x y z;

output out=want(drop=_:) max=;

run;

proc print data=want;run;

Re: long to wide with multiple key vars

Astounding — Tue, 14 Aug 2012 19:34:49 GMT

Or the lazy man's version of UPDATE:

data want;

update have (obs=0) have;

by id;

run;

It does assume that the data are sorted.

Re: long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 19:40:51 GMT

This is really close to what I need. but here's the snag I ran into when I tried your code (basically the value for y isn't coming through in the observation I want to keep (droprec = 0):

have
SAMPLEID	x	y	z	droprec

123	4			1
123		4		1
123				1
123			3	0
got
SAMPLEID	x	y	z	droprec

123	4			1
123	4	4		1
123	4			1
123	4		3	0

want
SAMPLEID	x	y	z	droprec

123	4			1
123	4	4		1
123	4			1
123	4	4	3	0

suggestions?

Thanks again for all the prompt replies. Much appreciated folks

Re: long to wide with multiple key vars

mkeintz — Tue, 14 Aug 2012 19:52:23 GMT

Thanks for the reminder.

Re: long to wide with multiple key vars

Reeza — Tue, 14 Aug 2012 19:53:50 GMT

Old school, but seems to be what you're after:

data have;

input ID X Y Z ;

cards;

123 . 8 .

123 7 . .

456 . 9 .

456 . . 2

124 . 4 .

124 4 . .

124 . . .

124 . . 3

;

data want;

set have;

by id notsorted;

retain val_x val_y val_z;

if first.id then call missing(val_x, val_y, val_z);

if x ne . then val_x=x;

if y ne . then val_y=y;

if z ne . then val_z=z;

run;

Re: long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 20:02:52 GMT

I got the following which is the same as the have dataset:

x	y	z	droprec

4	.	.	1
.	4	.	1
.	.	.	1
.	.	3	0

Re: long to wide with multiple key vars

Reeza — Tue, 14 Aug 2012 20:04:26 GMT

Look at the val_ variables instead, you can rename them if it makes you happy at the end.

Re: long to wide with multiple key vars

Apen — Tue, 14 Aug 2012 20:07:01 GMT

oh wow. I can't believe I missed that. hahaha. thanks so much! this is it!

Re: long to wide with multiple key vars

Astounding — Tue, 14 Aug 2012 20:09:16 GMT

Check again. The results you are describing would represent a bug in the software. UPDATE has been around for decades and it is not likely to have that sort of a bug in it!

Re: long to wide with multiple key vars

Linlin — Tue, 14 Aug 2012 20:12:51 GMT

Sorry. I don't think my first post work.

try this one:

data have;
input ID   X     y     Z droprec ;
cards;
123        4     .      .       1
123        .     4      .       1
123        .      .     .       1
123         .     .     3        0
;
options missing='.';

data want(drop= _:);
retain x y z;
set have;
by id;
if first.id then do;
_x=x; _y=y;_z=z; end;
else do;
x=ifn(x=.,_x,x);
y=ifn(y=.,_y,y);
z=ifn(z=.,_z,z);
end;
if last.id;
run;
proc print;run;
Obs _x _y _z ID

1 4 4 3 123

Re: long to wide with multiple key vars

ArtC — Tue, 14 Aug 2012 21:49:40 GMT

For more information on the UPDATE approach (with some alternate examples) shown by @Astounding take a look at:

https://communities.sas.com/thread/32502 and http://www.sascommunity.org/wiki/Tips:Using_UPDATE_to_Collapse_a_Data_Set

Re: long to wide with multiple key vars

Peter_C — Tue, 14 Aug 2012 22:10:13 GMT

I really think UPDATE is the way to go, you just have to pre-prepare an empty model

option obs=0 ;

data final_shape ;

set this that the other ; *list all transaction tables;

run ;

option obs= max ;

data view/view=view ;

set this that the other ;

by ID notsorted ;

run ;

data final_data ;

update final_shape view ;

by id notsorted ;

run ;

****** beware I haven't tested this ;