DATA Step, Macro, Functions and more

Sum in lastrow

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Sum in lastrow

 

Hi Guys,

 

I have these macro variables and have dataset

 

  Count Count1 Coutn2
Count_Start 60 50 52

 

Have Dataset :

 

 

Count Count1 Coutn2
12 22 1
10 22 1
8 22 1
6 22 1
4 22 1

 

 

want dataset:

 

  Count Count1 Coutn2
  12 22 1
  10 22 1
  8 22 1
  6 22 1
  4 22 1
Sum 40 110 5
Count_Start 60 50 52
Remain 20 -60 47

Accepted Solutions
Solution
‎04-01-2017 11:37 AM
Super User
Posts: 10,041

Re: Sum in lastrow


data have_c;
input Count Count1 Count2;
cards;
12 22 1
10 22 1
8 22 1
6 22 1
4 22 1
;
run;

%let Count_t =60;
%let Count1_t =50;
%let Count2_t=52;

proc sql;

select '           ' as x label='#',* from have_c
union all
select 'sum',sum(count) , sum(count1), sum(count2) from have_c
union all
select 'Count_Start',&count_t,&count1_t,&count2_t from have_c(obs=1)
union all
select 'Remain',&count_t-sum(count) , 
&count1_t-sum(count1), &count2_t-sum(count2) from have_c;
quit;



View solution in original post


All Replies
Super User
Posts: 11,343

Re: Sum in lastrow

Before we go very far on this what will the result be used for? It is generally a very bad idea to include summary values inside a data set as it is very possible for someone to run a procedure on the data and include those values as input.

 

Does the result need to be a data set or is this a report?

 

And what have you attempted so far?

Frequent Contributor
Posts: 84

Re: Sum in lastrow

I will use this dataset for my report. i wanted it as dataset if possible

 

I have tried this but this is giving column wise not row wise

 

data have_c;

 

input Count Count1 Count2;
cards;
12 22 1
10 22 1
8 22 1
6 22 1
4 22 1
run;

proc sql noprint;
select sum(count) , sum(count1), sum(count2) into: sum_count, :sum_count1, :sum_count2
from have_c;
quit;

%let Count_t =60;
%let Count1_t =50;
%let Count2_t=52;

data have2;
set have_c;
remain1 = &Count_t - &sum_count;
remain2 = &Count1_t - &sum_count1;
remain3 = &Count2_t - &sum_count2;
run;
proc print;
run;

Solution
‎04-01-2017 11:37 AM
Super User
Posts: 10,041

Re: Sum in lastrow


data have_c;
input Count Count1 Count2;
cards;
12 22 1
10 22 1
8 22 1
6 22 1
4 22 1
;
run;

%let Count_t =60;
%let Count1_t =50;
%let Count2_t=52;

proc sql;

select '           ' as x label='#',* from have_c
union all
select 'sum',sum(count) , sum(count1), sum(count2) from have_c
union all
select 'Count_Start',&count_t,&count1_t,&count2_t from have_c(obs=1)
union all
select 'Remain',&count_t-sum(count) , 
&count1_t-sum(count1), &count2_t-sum(count2) from have_c;
quit;



Frequent Contributor
Posts: 84

Re: Sum in lastrow

@KsharpThanks alot

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 186 views
  • 1 like
  • 3 in conversation