DATA Step, Macro, Functions and more

Add overall total to the data set

Reply
Contributor
Posts: 31

Add overall total to the data set

[ Edited ]

Hi all can you Please solve my small problem

data have;
input id name$ marks;
total+marks;
cards;
1 a 10
2 b 20
3 c 30
4 d 40
;
run;

in the above the last observation of total will 100

i want to make a new variable called V and put the last observation of total in every observation of V

  

i dont  want to hardcore the value i want to make it dynamic

 

output like this

 

 s.PNG

 

PROC Star
Posts: 7,360

Re: base sas

Here is one way:

data have;
input id name$ marks;
total+marks;
cards;
1 a 10
2 b 20
3 c 30
4 d 40
;

data want;
  do until (eof1);
    set have end=eof1;
    v+marks;
  end;
  do until (eof2);
    set have end=eof2;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Super User
Posts: 17,784

Re: base sas

I'm a fan of SQL for this:

 

proc sql;
create table want as
select *, sum(marks) as total
from have;
quit;

Another option is to calculate the mean in PROC MEANS and merge in the data:

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset

 

 

Contributor
Posts: 31

Re: base sas

Hi Reeza,

 

i know it from proc sql but i want to do it from data step only

Senior User
Posts: 1

Re: base sas

CEO Art297

Thanks for your inputs.

I have used the same code from CEO Art297  and slightly modified to get the expected output.

 

Shiva,

Can you please check this code. 

 

ata have;
input id name$ marks;
total+marks;
cards;
1 a 10
2 b 20
3 c 30
4 d 40
;

data want;
do until (eof1);
set have end=eof1;
v+marks;
end;
do until (last.marks);
set have ;
output;
end;
run;

proc print;

 

 


Capture.PNG
PROC Star
Posts: 7,360

Re: base sas

Why would you change the 2nd dow loop until condition? Yes, in this case it will produce the same result, but with a warning that the variable 'last.marks' has never been initialized.

 

And, if you included a 'by marks' statement in your revised code it would only produce one record.

 

I suggest you read up on what a DOW loop is and how it works (see: https://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwi1yJWywZXVA... )

 

Art, CEO, AnalystFinder.com

Ask a Question
Discussion stats
  • 5 replies
  • 217 views
  • 3 likes
  • 4 in conversation