How to write new rows from bottom to top?

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How to write new rows from bottom to top?

[ Edited ]

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*constrowNcol2*constrowNcol3*const
row3col1*constrow3col2*constrow3col3*const
row4col1*constrow4col2*constrow4col3*const
19.023.980.015

 

if const=355 then

 

The first calculated row would be:

 

19.02*355=6752.13.98*355=1412.90.015*355=5.325

 

And the first three: 

 

850933402.5178060722.5671083.125
2396995.5501579.51890.375
6752.11412.95.325
19.023.980.015

 

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


Accepted Solutions
Solution
‎04-18-2018 11:04 PM
PROC Star
Posts: 1,769

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

[ Edited ]
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 Smiley Happy 

View solution in original post


All Replies
PROC Star
Posts: 1,769

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


@Datinowrote:

 

 

rowNcol1*constrowNcol2*constrowNcol3*const
row3col1*constrow3col2*constrow3col3*const
row4col1*constrow4col2*constrow4col3*const
19.023.980.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!

Contributor
Posts: 34

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

[ Edited ]
Posted in reply to novinosrin

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.13.98*355=1412.90.015*355=5.325

 

And the first three: 

 

850933402.5178060722.5671083.125
2396995.5501579.51890.375
6752.11412.95.325
19.023.980.015
Solution
‎04-18-2018 11:04 PM
PROC Star
Posts: 1,769

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

[ Edited ]
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 Smiley Happy 

Contributor
Posts: 34

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

Posted in reply to novinosrin

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;
PROC Star
Posts: 1,769

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

@Datino That's correct. Well done!

Frequent Contributor
Posts: 103

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

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

Super User
Posts: 10,761

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

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;
Contributor
Posts: 34

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

Thank you, I like your approach.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 246 views
  • 3 likes
  • 4 in conversation