Solved: long to wide with multiple key vars - Page 2

mkeintz · Posted 08-14-2012 03:25 PM

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

Astounding · Posted 08-14-2012 03:34 PM

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.

mkeintz · Posted 08-14-2012 03:52 PM

Thanks for the reminder.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------