long to wide with multiple key vars

[mkeintz]

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.

[Astounding]

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]

Thanks for the reminder.

[ArtC]

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