Creating rows and derived variables in SAS

[martnic]

Hi  I'm trying to ''Stretch'' a set of data in SAS and was looking for the most straight forward method...its easy enough in excel but I'm looking to automate a manual process.

obs   column1

  01   5

  02   10

  03   15

  04   20

....

obs   column1

  01  5

  02  7.5 (new derived observation)

  03  10 (formerly obs '02')

  04  12.5 (new derived observation)

  05  15 (formerly obs '03')

  06  17.5 (new derived observation)

  07  20 (formerly obs '05')

...

Thanks!

[Reeza]

And what are the rules? You're doubling the observations? Or are you trying to add in missing data points of a series?

To automate a process, the process first needs to be documented and then it can be automated.

---

@martnic wrote:

Hi  I'm trying to ''Stretch'' a set of data in SAS and was looking for the most straight forward method...its easy enough in excel but I'm looking to automate a manual process.

obs   column1

  01   5

  02   10

  03   15

  04   20

....

obs   column1

  01  5

  02  7.5 (new derived observation)

  03  10 (formerly obs '02')

  04  12.5 (new derived observation)

  05  15 (formerly obs '03')

  06  17.5 (new derived observation)

  07  20 (formerly obs '05')

...

Thanks!

---

[martnic]

Excellent question! I'm doubling the number of observations and creating a mean derived data point between each of the existing observations. The process is then repeated four times (i.e. 12 observations becomes 192 observations).

[novinosrin]

data have;
input obs   column1;
datalines;
  01   5
  02   10
  03   15
  04   20
;

data want;
set have end=last;
output;
if not last then do;
column1=column1+2.5;
output;
end;
run;

[Astounding]

My observation count is different than yours.  If you start with 12 observations, you could end up with either 22 or 23 after one iteration.  It depends on whether you want to add an observation after the 12th observation.  So if you tell us that much, the programming can begin.

[martnic]

Sorry for the poor communication\detail, its my first time using these boards...

In my excel tables I go from 12 observations to 24, from 24 to 48, from 48 to 96, and from 96 to 192. (12^4=192).

in my first iteration obs 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 become obs 1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, and 23 with obs 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, and 24 being derived as the mean between the two starting obs. (i.e. obs2 = average(obs1,obs3), obs4 = average(obs3, obs5)...obs24 = average(obs23, obs25*)

*obs25 is a blank row with a value of 0

[novinosrin]

data have;
input obs   column1;
datalines;
  01   5
  02   10
  03   15
  04   20
;

data want;
set have end=last;
_k=lag(column1);
if _n_>1 then do;
_col=column1;
column1=mean(_k,column1);
output;
column1=_col;
output;
end;
else output;
drop _:;
run;

[Astounding]

Yes.  Plus:

if last then do;

   column1 = column1/2;

   output;

end;

Then a new step:

data want;

set want (drop=obs);

obs = put(_n_, z3.);

run;