topic Re: How to write new rows from bottom to top? in SAS Programming

How to write new rows from bottom to top?

Datino — Wed, 18 Apr 2018 17:58:28 GMT

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?

Re: How to write new rows from bottom to top?

novinosrin — Wed, 18 Apr 2018 17:43:37 GMT

@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!

Re: How to write new rows from bottom to top?

Datino — Wed, 18 Apr 2018 17:57:19 GMT

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

Re: How to write new rows from bottom to top?

novinosrin — Wed, 18 Apr 2018 18:19:33 GMT

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 🙂

Re: How to write new rows from bottom to top?

Datino — Wed, 18 Apr 2018 21:38:56 GMT

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;

Re: How to write new rows from bottom to top?

novinosrin — Wed, 18 Apr 2018 21:41:18 GMT

@Datino That's correct. Well done!

Re: How to write new rows from bottom to top?

MarkWik — Thu, 19 Apr 2018 00:39:27 GMT

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

Re: How to write new rows from bottom to top?

Ksharp — Thu, 19 Apr 2018 12:43:35 GMT

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;

Re: How to write new rows from bottom to top?

Datino — Fri, 20 Apr 2018 13:50:09 GMT

Thank you, I like your approach.