How to write new rows from bottom to top?

[Datino]

So I have a SAS Dataset that looks like this:

data tmp;
	input val1 val2;
	datalines;
19.02 3.98
;
run;

And I want to add new calculated rows on top of the one I have. Say I want to add 4 more rows, the one I already have would be observation #5. So I want to add observation #4, which is calculated with the values from obs #5, obs #3 is calculated from obs#4 and so on. Like this:

rowNcol1*const	rowNcol2*const	rowNcol3*const
row3col1*const	row3col2*const	row3col3*const
row4col1*const	row4col2*const	row4col3*const
19.02	3.98	0.015

if const=355 then

The first calculated row would be:

19.02*355=6752.1

3.98*355=1412.9

0.015*355=5.325

And the first three: 

850933402.5	178060722.5	671083.125
2396995.5	501579.5	1890.375
6752.1	1412.9	5.325
19.02	3.98	0.015

What would be the best way to do this with SAS code?

[novinosrin]

---

@Datinowrote:

rowNcol1*const	rowNcol2*const	rowNcol3*const
row3col1*const	row3col2*const	row3col3*const
row4col1*const	row4col2*const	row4col3*const
19.02	3.98	0.015

What would be the best way to do this with SAS code?

---

Can you please post clear figures of your expected output? Thank you!

[Datino]

Surely, thank you for replying.

const=355

The first calculated row would be (just the numeric results, I added the whole operation for clarity):

19.02*355=6752.1

3.98*355=1412.9

0.015*355=5.325

And the first three: 

850933402.5	178060722.5	671083.125
2396995.5	501579.5	1890.375
6752.1	1412.9	5.325
19.02	3.98	0.015

[novinosrin · Accepted solution]

data tmp;
	input val1 val2;
	datalines;
19.02 3.98
;
run;
%let const=355;
%let rows_to_add=4;
data _null_;
if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("_n_") ;
   h.definedata ("val1", "val2") ;
   h.definedone () ;
end; 
set tmp;
_n_=5;
h.add();
do _n_= &rows_to_add to 1 by -1;
val1=val1*&const;
val2=val2*&const;
h.add();
output;
end;
h.output(dataset:'want');
run;

I did the above as a matter of fun. However, if you want a robust code run in  production environment, we shall discuss and I am happy to help you with that, although this seemed like a simple quiz/interview question 🙂 

[Datino]

Thanks, it's not for a production environment, I'm just trying to convert some stuff I did in Excel to SAS.

I found that increasing the value of rows_to_add above 4 produces a duplicate key error. Got around that by changing line 17 from your code to

_n_=&rows_to_add. +1;

[novinosrin]

@Datino That's correct. Well done!

[MarkWik]

Hi @Datino If your question has been answered, you could close the thread marking the question as answered

[Ksharp]

data tmp;
	input val1 val2;
	datalines;
19.02 3.98
;
run;
%let const=355;
%let rows_to_add=4;

data want;
 set tmp;
 i=0;output;
    do i=1 to &rows_to_add;
      val1=val1*&const;val2=val2*&const;output;    
    end;
run;
proc sort data=want;
 by descending i;
run;
proc print noobs;run;

[Datino]

Thank you, I like your approach.