creating an observation combining different records?

Reply
Frequent Contributor
Posts: 75

creating an observation combining different records?

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.

Super User
Super User
Posts: 7,682

Re: creating an observation combining different records?

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;

Ask a Question
Discussion stats
  • 1 reply
  • 167 views
  • 0 likes
  • 2 in conversation