How to sum up data in batches by a var

[blyssz]

I have a dataset in the format :


A1,A2,….A18,B1,B2…..B18, Mon1, Mon2.....Mon18 Mon
1 15.....13 25 27...34 2009-12 2009-11 2008-08 17
I need to sum up data in batches of 6 month in the format:

1-6Mon 7-12Mon 13-18Mon Total
Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum(B1-B18)

How can I do that?
Thanks,

Blyssz

 

[kdt82]

Hi Blyssz,

From your example output, it looks like you are missing an Identifier - how will you be able to identify the A records from the B records?

The output that want is quite common in clinical reporting, however the structure of your original data isn't. So the bulk of work will be to get the data into a structure that will allow for flexible reporting in SAS. As mentioned in a previous thread, this means we want the data long (normalized).

Assuming a starting dataset of :

data have ;
   input A1-A18 B1-B18 @;
cards ;
1 23 43 5 34 43 43 43 43 34 6 76 787 4 45 656 86 565
45 4 544 5 4 45 68 99 8  56 54 54 45 4 54 45 2 111
;;;;
title 'Starting dataset' ;
proc print ;run ;

We have first need to make the data long, both proc transpose are good for this.

data rigid ;
  set have ;
  array A_range [*] A1-A18 ;
  array B_range [*] B1-B18 ;
  array h_range [*] X1-X18 ;
  id = 'A' ;*populate array with A values ;
  do _i_=1 to dim(h_range) ;
     h_range[_i_] = A_range[_i_] ;
	 end ;
  output ;
  id = 'B' ;*populate array with B values ;
  do _i_=1 to dim(h_range) ;
     h_range[_i_] = B_range[_i_] ;
	 end ;
  output ;
  keep id X: ;
  run ;
proc print ;run  ;

The next step will be to use the range modifiers available in SAS 9 onwards (If you don't have SAS >9 then you have to specify the sum arguments as sum(A1,A2,A3....)).

data final ;
   set rigid ;
   Mon1_6   = sum(of X1-X6  ) ;
   Mon7_12  = sum(of X7-X12 ) ;
   Mon13_18 = sum(of X13-X18) ;
   Total    = sum(of X1-X18 ) ;
   drop X: ;
   run; 
title 'Final layout' ;
proc print;run ;

Given the option, it is good practice to make the code as flexible as possible. In the above code, we had to know the ranges and 'hard code' in the variable names and the ranges.

The following code is an example of how to make the reporting more flexible, requiring only to pass the incremenets (here every 6 months) in a macro variable.

%let incrementMonth = 6 ;*specify everyN records to split by ;
data stats0 ;
   length id $1 ;
   _sum = 0 ;
   do  _n_=1 to &incrementMonth  ;
      set trans ;
	  id = _name_ ;
	  _start = min(_start,compress(_name_,,'kd')) ;
	  _end   = max(_start,compress(_name_,,'kd')) ;
	  _sum =_sum + col1 ;
	  end ;
   month = cats('Mon',_start,'_',_end) ;*Dynamically create variable labels based on data ;
   run ;
data total ;
  do until (last.id) ;
     set stats0 ;
     by id ;
     total + _sum ;
	 output ;
	 end;
  month = 'Total' ;*Add total row to each ID ;
  _sum = total ;
  output ;
  run ;
proc transpose data=total out=stats1(drop=_:);
   by id ;
   id month ;
   var _sum ;
   run;
title 'Final layout' ;
proc print ;run ;
title '';

 

[blyssz]

Thanks a lot kdt82.
Is it possible to create Month variable for months batches as I want to create a pivot table in excel with the output dataset and it will be easier to arrange the months if there is a variable for that.


Thanks,
Blyssz

 

[kdt82]

Can you provide an example of what you want the output to look like?

 

[blyssz]

Actually I need to create a pivot table in excel with the final dataset , so I need to create Month as a field variable that displays 1-6Mon,7-12Mon as data variables so that I can just drag the Month variable in the column field.

The final dataset after transposing data look like
State Section School id 1-6Mon 7-12Mon 13-18Mon Total
IL Primary Sch1 A Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
IL Primary Sch1 B Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum(B1-B18)
IL Primary Sch2 A Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
IL Primary Sch2 B Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum(B1-B18)
IL Secondary Sch3 A Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
IL Secondary Sch3 B Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum(B1-B18)
CA Primary Sch3 A Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
CA Primary Sch3 B Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum (B1-B18)

My output should look like :

State
Section
School

1-6Mon 7-12Mon 13-18Mon Total
A Sum(A1-A6) Sum(A7-A12) Sum(A13-A18) Sum (A1-A18)
B Sum(B1-B6) Sum(B7-B12) Sum(B13-B18) Sum(B1-B18)

Where State, Section and School are page Variables, id (A, B) as row variable and Months are column variables.
I have 50 state, 5 Sections and 45 schools that may have branches in 1 or more that 1 school.
For a particular state, if user selects a particular section then I need to display values for school in the page section.


i.e. if State IL has 2 different schools for primary section then it should display the names of 2 school in the School category in pivot table.
I.e in the pivot table I want to display the 3 category school selection based on the combination of State and section.

If State=”IL” then
If Section=”Primary” then
School=”sch1”, “sch2”

If I select state “CA” and section as Primary then it should display only Sch3 in school list or if I select State =”IL” and section =”Secondary” then it should display sch3 in the dropdown list.
So I was wondering, do I need to write statements like for each State and all sections and school within that state or can we use macros to solve this problem.

I am new to macros so don’t have any idea If it is possible with macros or not.

Thanks,
Blyssz

 

[kdt82]

For what you are wanting, it will be much easier to export the data to excel and then either create the pivot table manually, or via vba.

http://vbadud.blogspot.com/2008/05/create-pivot-table-using-excel-vba.html

Another alternative, is to generate the vba via SAS and call the vbs code. An example is given in this paper

http://www.nesug.org/proceedings/nesug05/pm/pm1.pdf

Unfortunately I work on a Unix mainframe, so I cannot provide any tested code.

 

[blyssz]

Thanks kdt82 for the links.Those are really helpful

I was wondering can I use DDE to create Excel workbook with 50 tabs for each state?

Thanks,
Blyssz

 

[kdt82]

Don't know much about DDE, but normally if I had to do some heavy VBA manipulation, I prefer to code in the spreadsheets VBA editor directly.

You can however use the ODS Excel tagset to create the named tabs. You might want to read up on the styles you can use with this for further customization.

In terms of dynamically generating the code required for your 50 states, I would recommend using the 'wallpaper' method. This essentially involves printing the code out to a temporary file, and then executing it (as you would a macro). Here is an example using the sashelp.class dataset:

HTH

*Going to generate the code to rename the worksheets and print out the records for each name;

*Replace this with your absolute path for the output eg: C:\documents.... ;
%let path = %sysfunc(pathname(pgm_a))/ ; 
%put NOTE:  output will be sent to the &path folder ;

filename temp temp;*Create a tempory file to write the code out to ;

* Generate code for each name in the dataset ;
data _null_ ;
   file temp ;
   set sashelp.class ;
   put 'ods tagsets.ExcelXP options(sheet_name="' name '");';
   put 'proc print data=sashelp.class(where=(name="' name '"));' ;
   put +3 'run ;' ;
   run ;

options center ;
title ; *Clear title and footnotes ;
footnote ;

ods listing close ;
ods tagsets.ExcelXP path="&path" file='class.xls' ;
%include temp / source2 ; *include the generated code and print to log ;
ods tagsets.ExcelXP close ;