Summarise Repeated Values

[Toni2]

hi all,

I am new SAS and i need some help

I have the below dataset and i want to create a new table where to summarise for each month the volume of each bt option. For example, for month 1 we have 24 FTB, for month 2 we 2 FTB and so on. We need the same for all the options of bt variable

Please note the variable month takes value from 1 to 300

Month	bt	volume
1	FTB	12
1	FTB	12
1	SWI	17
1	OTH	12
2	FTB	1
2	RMG	1
2	FTB	1
2	OTH	1
3	FTB	2
3	RMG	2
3	SWI	3
3	OTH	2
4	FTB	195
4	RMG	190
4	SWI	269
4	OTH	188

[PaigeMiller · Accepted solution]

I assume that by "summarize" you mean "sum".

Try this:

proc summary nway data=have;
    class month bt;
    var volume;
    output out=_sums_ sum=volume_sum;
run;

[ballardw]

You may want to consider turning a sequential "month" into an actual SAS date value indicating the first day of the month.

There are many  ways to "summarize" based on formats of date values that require much more work with a sequential value you currently have..

For example if the "month" were an actual date value you could generate a calendar quarterly summary by using @PaigeMiller's approach by adding a format for the month date value such as:

proc summary nway data=have;
    class month bt;
    format month yymon.;
    var volume;
    output out=_sums_ sum=volume_sum;
run;

 Or make a calendar year summary by

proc summary nway data=have;
    class month bt;
    format month year.;
    var volume;
    output out=_sums_ sum=volume_sum;
run;

You could even make a custom format that reflects specific time intervals such as Fiscal Years instead of calendar years.

Use of actual dates also allows for making more human readable data. Month 27 doesn't tell me much in an analysis. Jun2002 does. Graphs of data with dates also allow grouping by formatted values or creating "nice" axis labels and control displayed values with options like values = (01Jan2000 to 01Jan2010 by quarter).

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[Toni2]

hi thank you for your response as well. Yes, i need this for the dates since doesn't make sense to the analysis

[ballardw]

If a calendar date would be helpful it is pretty easy to add assuming all of the "month" values are indicating interval since some given date:

data have;
  input Month	bt $	volume;
datalines;
1	FTB	12
1	FTB	12
1	SWI	17
1	OTH	12
2	FTB	1
2	RMG	1
2	FTB	1
2	OTH	1
3	FTB	2
3	RMG	2
3	SWI	3
3	OTH	2
4	FTB	195
4	RMG	190
4	SWI	269
4	OTH	188
;

data example;
   set have;
  /* assuming the first calendar month before the data was collected
     was Dec 1990 just for example:
  */
  CalendarMonth = intnx('month','01DEC1990'd,1,'b');
  format CalendarMonth date9.;
run;

The only "tricky" parts are indicating the month before the start of your data so you can increment using the month number. The date for that month value would be provided in the 'ddMONyyyy'd format as shown in the INTNX function. That function is used to increment date, time, or date time values.

The second not very tricky part is to assign a format to the created date variable so people can understand it.

You could then test the alternate summaries I provided earlier, using the proper created variable in the Example data set with the formats shown.

[Toni2]

yes, this is something that it works for me, thanks for the support 🙂