creating an observation combining different records?

[MarkWik]

I'd like to understand how to combine obs from different records into a single observation in the output?For example, in sashelp.class or using some of your own input, can anybody demonstrate with a code how to accomplish the same?

1. combine 4,6,and 8 to the 3rd observation?

2. combine 2nd obs, and last observation to 1st obs.

What i really am after is i wanna understand how you look prev once you are past that iteration in the implied datastep loop. Examples i would appreciate is doing the sample in 1. multiple pass and 2. in single pass. Any kind of simple, medium and complex examples will really help.

[RW9]

Hi,

Provide some test data and required output.  There are many logical methods of looking up observations which fit into many differing scenarios - hash tables, merge statements, have a set in a conditional branch, sub-querying in sql, lag() function, by group processing, arrays etc.  Say for instance your first options - combine 4,6,8 to 3rd observation - what does this actually mean.  The position of a row in a dataset is purely arbitrary, so if I sort that dataset maybe row 8 will be in position 3.  I wouldn't recommend to use arbitrary row number as a means to identify specific data.

If however row 4, 5, 6 contains a id variable for "sum" and row 3 has the id "total" then we could do:

data have;

  idvar="not used"; output;

  idvar="not used"; output;

  idvar="total"; result=.; output;

  idvar="sum"; result=4; output;

  idvar="sum"; result=3; output;

  idvar="sum"; result=6; output;

run;

proc sql;

  create table WANT as

  select  IDVAR,

          (select SUM(RESULT) from WORK.HAVE where IDVAR="sum") as RESULT

  from    WORK.HAVE

  where   IDVAR="total";

quit;