DATA Step, Macro, Functions and more

Creating rows and derived variables in SAS

Reply
New Contributor
Posts: 3

Creating rows and derived variables in SAS

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!

Super User
Posts: 23,224

Re: Creating rows and derived variables in SAS

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!


 

New Contributor
Posts: 3

Re: Creating rows and derived variables in SAS

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).

PROC Star
Posts: 1,547

Re: Creating rows and derived variables in SAS

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;
Super User
Posts: 6,622

Re: Creating rows and derived variables in SAS

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.

New Contributor
Posts: 3

Re: Creating rows and derived variables in SAS

Posted in reply to Astounding

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

PROC Star
Posts: 1,547

Re: Creating rows and derived variables in SAS

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;
Super User
Posts: 6,622

Re: Creating rows and derived variables in SAS

Posted in reply to novinosrin

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;

 

Ask a Question
Discussion stats
  • 7 replies
  • 118 views
  • 0 likes
  • 4 in conversation