topic Re: Creating rows and derived variables in SAS in SAS Programming

Creating rows and derived variables in SAS

martnic — Tue, 06 Feb 2018 19:01:27 GMT

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!

Re: Creating rows and derived variables in SAS

Reeza — Tue, 06 Feb 2018 19:03:47 GMT

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!

Re: Creating rows and derived variables in SAS

martnic — Tue, 06 Feb 2018 19:11:00 GMT

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

Re: Creating rows and derived variables in SAS

novinosrin — Tue, 06 Feb 2018 19:15:36 GMT

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;

Re: Creating rows and derived variables in SAS

Astounding — Tue, 06 Feb 2018 19:26:24 GMT

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.

Re: Creating rows and derived variables in SAS

martnic — Tue, 06 Feb 2018 19:50:19 GMT

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

Re: Creating rows and derived variables in SAS

novinosrin — Tue, 06 Feb 2018 19:56:18 GMT

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;

Re: Creating rows and derived variables in SAS

Astounding — Tue, 06 Feb 2018 20:24:24 GMT

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;