CODEWITHSUNDEEP
sas
sahil khurana
sahil khurana

Reputation: 1

how to vertically sum a range of dynamic variables in sas?

I have a dataset in SAS in which the months would be dynamically updated each month. I need to calculate the sum vertically each month and paste the sum below, as shown in the image.

Proc means/ proc summary and proc print are not doing the trick for me.

I was given the following code before:

`%let month = month name;
%put &month.;

data new_totals;
set Final_&month. end=end;  
&month._sum + &month._final;    
/*feb_sum + &month._final;*/
output;                
if end then do;         
measure = 'Total';    
&month._final = &month._sum;  
/*Feb_final = feb_sum;*/
output;               
end;
drop &month._sum;   
run; `

The problem is this has all the months hardcoded, which i don't want. I am not too familiar with loops or arrays, so need a solution for this, please.

enter image description here

Upvotes: 0

Views: 378

Answers (2)

PeterClemmensen
PeterClemmensen

Reputation: 4937

Here is a basic way. Borrowed sample data from Richard.

data have;
  length group $20;
  do group = 'A', 'B';
    array months jan2020 jan2019 feb2020 feb2019 mar2019 apr2019 may2019 jun2019 jul2019 aug2019 sep2019 oct2019 oct2019 nov2019 dec2019;
    do over months;
      months  = 10 + floor(rand('uniform', 60, 1));
    end;
    output;
  end;
run;

proc summary data=have;
    var _numeric_;
    output out=temp(drop=_:) sum=;
run;

data want;
    set have temp (in=t);
    if t then group='Total';
run;

Upvotes: 0

Richard
Richard

Reputation: 27508

It may be better to use a reporting procedure such as PRINT or REPORT to produce the desired output.

data have;
  length group $20;
  do group = 'A', 'B', 'C';
    array month_totals jan2020 jan2019 feb2020 feb2019 mar2019 apr2019 may2019 jun2019 jul2019 aug2019 sep2019 oct2019 oct2019 nov2019 dec2019;
    do over month_totals;
      month_totals  = 10 + floor(rand('uniform', 60));
    end;
    output;
  end;
run;

ods excel file='data_with_total_row.xlsx';

proc print noobs data=have;
  var group ;
  sum jan2020--dec2019;  
run;

proc report data=have;
  columns group jan2020--dec2019;
  define group / width=20;
  rbreak after / summarize;
  compute after;
    group = 'Total';
  endcomp;
run;

ods excel close;

enter image description here

Data structure

The data sets you are working with are 'difficult' because the date aspect of the data is actually in the metadata, i.e. the column name. An even better approach, in SAS, is too have a categorical data with columns

  • group (categorical role)
  • month (categorical role)
  • total (continuous role)

Such data can be easily filtered with a where clause, and reporting procedures such as REPORT and TABULATE can use the month variable in a class statement.

Example:

data have;
  length group $20;
  do group = 'A', 'B', 'C';
    do _n_ = 0 by 1 until (month >= '01feb2020'd);
      month = intnx('month', '01jan2018'd, _n_);
      total = 10 + floor(rand('uniform', 60));
      output;
    end;
  end;
  format month monyy5.;
run;

proc tabulate data=have;
  class group month;
  var total;
  table 
    group all='Total'
    ,
    month='' * total='' * sum=''*f=comma9.
  ;
  where intck('month', month, '01feb2020'd) between 0 and 13;
run;


proc report data=have;
  column group (month,total);
  define group / group;
  define month / '' across order=data ;
  define total / '' ;

  where intck('month', month, '01feb2020'd) between 0 and 13;
run;

Upvotes: 1

Related Questions