topic Summarise data with months down the side, years across the top, and difference from prev mth inside in SAS Programming

Summarise data with months down the side, years across the top, and difference from prev mth inside

Jamie_H — Fri, 27 Nov 2020 16:05:36 GMT

I have non summarised data (see SASHELP.CITIDAY for a decent looking comparison).

I would like to end up with a report that shows the months down the side (month being from the Date variable in that dataset), Years across the top (again from the Date variable) and the values inside being the %difference for the sum of variable SNYDJCM from one month to the next.

For example in January 1988, the sum of SNYDJCM was 14444.12, for February it was 14806.24 and March was 17629.16.

In the output, the value for Jan 1988 should be blank as it was the first month so had no growth, Feb 1988 should be 2.51% and Mar 1988 19.07%, and so on and so forth for all the months/years in the dataset.

I've attached a spreadsheet showing this desired output.

Is anyone able to assist in the best way to do this? Is it possible with some sort of sql code or will it require a summary procedure with (i'm guessing) some transposing?

Many thanks

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

ballardw — Fri, 27 Nov 2020 16:59:19 GMT

This is apparently not one of the standard training data sets supplied with all installs, maybe it comes with SAS/ETS or similar module I don't have.

So I can't write code against the set.

My approach, from the description, would be

1) get the totals my calendar month, Proc summary with the Date variable formatted with a year and month only format (your choice) as a class or by variable.

2) in a data step compare the monthly totals, add a variable with the desired comparison calculation, using the Lag function and a year variable (report procedures will want a different variable to do the columns.

No transpose needed unless you want to make a hard to use data set. Take the data as made in step to into either Proc report or Proc tabulate

I don't open xlsx from unknown sources so I am afraid that's about as far as I can go.

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

PaigeMiller — Fri, 27 Nov 2020 17:10:31 GMT

PROC SUMMARY to get the sums, followed by a DATA step to compute the differences, follow by PROC REPORT to obtain the final report.

Long data sets are to be created and used here. Only the final PROC REPORT is needed to get the wide report.

Also, many of us, including me, will not download and open your Microsoft Excel file, as Microsoft Office files can be security threats. If you provide (a portion of) the data via these instructions, and not via any other method, I could give more details to my solution.

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

Jamie_H — Fri, 27 Nov 2020 17:20:31 GMT

Thank you Paige,

Completely understandable about the virus potential. I tried attached a picture of it first, but the website wont allow that. I've copied the output into a .txt file and attached

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

Jamie_H — Fri, 27 Nov 2020 17:31:39 GMT

In addition, for those of you without access to that particular dataset in SASHELP, here is the first 13 months of data:

data have;
input date :date9. SNYDJCM;
format date date9.;
datalines;
01Jan1988
04Jan1988 740.1999998
05Jan1988 747.3799992
06Jan1988 750.3999996
07Jan1988 757.04
08Jan1988 711.3599997
11Jan1988 721.9199991
12Jan1988 715.1999998
13Jan1988 713.5899992
14Jan1988 708.3499994
15Jan1988 721.8099995
18Jan1988 724.4299994
19Jan1988 718.29
20Jan1988 701.5899992
21Jan1988 700.6999998
22Jan1988 709.4599991
25Jan1988 721.5199995
26Jan1988 714.0499992
27Jan1988 712.9399996
28Jan1988 722.4899998
29Jan1988 731.3999996
01Feb1988 731.3999996
02Feb1988 727.6599998
03Feb1988 731.2199993
04Feb1988 720.4799995
05Feb1988 720.3399992
08Feb1988 717.3599997
09Feb1988 712.9799995
10Feb1988 733.0099993
11Feb1988 731.1499996
12Feb1988 736.6399994
15Feb1988
16Feb1988 744.7599993
17Feb1988 744.6699991
18Feb1988 741.2199993
19Feb1988 753.0499992
22Feb1988 760.9899998
23Feb1988 759.8999996
24Feb1988 759.0999994
25Feb1988 754.1699991
26Feb1988 756.3799992
29Feb1988 769.7599993
01Mar1988 767.6999998
02Mar1988 769.7599993
03Mar1988 771.0299997
04Mar1988 769.5499992
07Mar1988 767.9499998
08Mar1988 774.04
09Mar1988 772.5899992
10Mar1988 756.8899994
11Mar1988 760.8099995
14Mar1988 764.5099993
15Mar1988 764.5799999
16Mar1988 774.9599991
17Mar1988 781.1399994
18Mar1988 782.2299995
21Mar1988 776.4499998
22Mar1988 779.1099997
23Mar1988 781.3999996
24Mar1988 764.3499994
25Mar1988 749.3499994
28Mar1988 748
29Mar1988 754.1799994
30Mar1988 747.7099991
31Mar1988 750.8699999
01Apr1988
04Apr1988 747.1299992
05Apr1988 751.5999994
06Apr1988 771.8399992
07Apr1988 771.6199999
08Apr1988 781.3999996
11Apr1988 780.9599991
12Apr1988 785.2799997
13Apr1988 782.7799997
14Apr1988 748.7599993
15Apr1988 747.8899994
18Apr1988 746.9499998
19Apr1988 745.2399998
20Apr1988 739.0299997
21Apr1988 740.4799995
22Apr1988 752.0699997
25Apr1988 758.2099991
26Apr1988 760.1399994
27Apr1988 759.2999992
28Apr1988 756.3199997
29Apr1988 754.9399996
02May1988 759.6299992
03May1988 763.9499998
04May1988 758.2799997
05May1988 752.9099998
06May1988 748.7599993
09May1988 744.1099997
10May1988 746.29
11May1988 733.6799994
12May1988 733.3599997
13May1988 739.6399994
16May1988 745.5699997
17May1988
18May1988 723.5899992
19May1988 726.2399998
20May1988 725.25
23May1988 719.2999992
24May1988 727.29
25May1988 727.1799994
26May1988 730.1699991
27May1988 729.4099998
30May1988
31May1988 754.2799997
01Jun1988 766.3899994
02Jun1988 764.9499998
03Jun1988 771.1199999
06Jun1988 772.7399998
07Jun1988 766.7999992
08Jun1988 783.9499998
09Jun1988 780.8799992
10Jun1988 784.0199995
13Jun1988 782.6599998
14Jun1988 791.25
15Jun1988 792.1999998
16Jun1988 779.75
17Jun1988 782.0899992
20Jun1988 775.7699995
21Jun1988 785.1999998
22Jun1988 799.0499992
23Jun1988 799.0799999
24Jun1988 799.8799992
27Jun1988 787.1999998
28Jun1988 794.5799999
29Jun1988 791.3599997
30Jun1988 801.5799999
01Jul1988 796.7799997
04Jul1988
05Jul1988 805.9299994
06Jul1988 794.6899996
07Jul1988 793.0999994
08Jul1988 787.5799999
11Jul1988 789.1699991
12Jul1988 782.6999998
13Jul1988 787.0099993
14Jul1988 789.2399998
15Jul1988 794.1599998
18Jul1988 789.1699991
19Jul1988 782.3199997
20Jul1988 787.1299992
21Jul1988 777.7399998
22Jul1988 767.9799995
25Jul1988 770.7399998
26Jul1988 772.4399996
27Jul1988 766.7699995
28Jul1988 776.1099997
29Jul1988 794.3899994
01Aug1988 795.79
02Aug1988 795.9799995
03Aug1988 796.3199997
04Aug1988 793.75
05Aug1988 790.9899998
08Aug1988 787.5
09Aug1988 776.6399994
10Aug1988 761.2399998
11Aug1988 762.4899998
12Aug1988 762.4099998
15Aug1988 750.9099998
16Aug1988 755.9399996
17Aug1988 757.7999992
18Aug1988 757.7199993
19Aug1988 756.3999996
22Aug1988 747.8099995
23Aug1988 746.8999996
24Aug1988 759.8399992
25Aug1988 754.0099993
26Aug1988 755.4499998
29Aug1988 763.8899994
30Aug1988 762.8699999
31Aug1988 761.0499992
01Sep1988 752.0099993
02Sep1988 769.8699999
05Sep1988
06Sep1988 775.0899992
07Sep1988 774.2999992
08Sep1988 773.6899996
09Sep1988 776.6399994
12Sep1988 774.9399996
13Sep1988 778.3499994
14Sep1988 783.7199993
15Sep1988 780.1999998
16Sep1988 783.8699999
19Sep1988 780.8799992
20Sep1988 784.0599995
21Sep1988 785.7999992
22Sep1988 782.4299994
23Sep1988 785.8399992
26Sep1988 784.29
27Sep1988 783.6399994
28Sep1988 785.9499998
29Sep1988 797.5699997
30Sep1988 795.4499998
03Oct1988 795
04Oct1988 794.6899996
05Oct1988 797.9499998
06Oct1988 797.6099997
07Oct1988 812.7799997
10Oct1988 812.6299992
11Oct1988 811.6899996
12Oct1988 799.9199991
13Oct1988 802.9299994
14Oct1988 802.9299994
17Oct1988 805.1099997
18Oct1988 813.0799999
19Oct1988 804.9199991
20Oct1988 820
21Oct1988 821.3599997
24Oct1988 816.9199991
25Oct1988 818.8499994
26Oct1988 816.8099995
27Oct1988 807.5899992
28Oct1988 809.9799995
31Oct1988 811.4199991
01Nov1988 813.2399998
02Nov1988 814.9799995
03Nov1988 819.2299995
04Nov1988 810.8499994
07Nov1988 803.4099998
08Nov1988 806.5299997
09Nov1988 802.54
10Nov1988 802.0899992
11Nov1988 786.6499996
14Nov1988 786.1499996
15Nov1988 788.4299994
16Nov1988 775.2999992
17Nov1988 778.7599993
18Nov1988 781.9399996
21Nov1988 783.1899996
22Nov1988 786.1199999
23Nov1988 794.6499996
24Nov1988
25Nov1988 787.75
28Nov1988 792.6399994
29Nov1988 800.1499996
30Nov1988 805.5799999
01Dec1988 801.8999996
02Dec1988 799.1999998
05Dec1988 809.29
06Dec1988 815.1699991
07Dec1988 816.9599991
08Dec1988 812.5899992
09Dec1988 815.0999994
12Dec1988 813.5099993
13Dec1988 813.7699995
14Dec1988 812.1399994
15Dec1988 812.6699991
16Dec1988 818.7599993
19Dec1988 826.25
20Dec1988 823.1299992
21Dec1988 822.25
22Dec1988 822.0999994
23Dec1988 825.0299997
26Dec1988
27Dec1988 823.0099993
28Dec1988 824.1899996
29Dec1988 830.2399998
30Dec1988 825.9399996
02Jan1989
03Jan1989 816.9499998
04Jan1989 830.3399992
05Jan1989 832.7099991
06Jan1989 835.0799999
09Jan1989 837.2599993
10Jan1989 835.9399996
11Jan1989 841.3699999
12Jan1989 845.6399994
13Jan1989 846.3399992
16Jan1989 846.0699997
17Jan1989 845.9499998
18Jan1989 856.5499992
19Jan1989 856.1699991
20Jan1989 853.5299997
23Jan1989 848.9399996
24Jan1989 861.1399994
25Jan1989 863
26Jan1989 871.8899994
27Jan1989 881.4799995
30Jan1989 882.8799992
31Jan1989 891.1199999
;
run;

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

ballardw — Fri, 27 Nov 2020 17:49:01 GMT

You data step needs an: Infile datalines truncover ; (or missover) because of missing values.

This may get you started.

proc summary data=have nway;
   class date;
   format date monyy.;
   var SNYDJCM;
   output out=summary (drop=_:) sum=;
run;

data want;
   set summary;
   lsynd = lag(SNYDJCM);
   year = year(date);
   change = (SNYDJCM - lsynd)/SNYDJCM;
run;

proc report data=want;
   columns date year, change;
   define date / group order=internal 
                format=monname.  'Month';
   define year/ across;
   define change/ format=percent8.1 ;
run;

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

quickbluefish — Fri, 27 Nov 2020 17:49:32 GMT

Hi there -

You can just do this with a DATA step with temporary arrays -- first get the start and end year into macro variables, then the data step that follows. The first data step just produces some test input - just swap out TEST for your actual data. Hope this helps.

/* this is just example input -- replace with your actual data */
data test;
length dt val 8;
format dt date9. val dollar8.2;
do dt=10000 to 15000;
  val=ranuni(0)*1000;
  output;
end;
run;
/* end of example input */

proc sql noprint;
select year(min(dt)) into :sy from test;
select year(max(dt)) into :ey from test;
quit;

%let sy=%sysfunc(compress(&sy));
%let ey=%sysfunc(compress(&ey));

data monyr;
set test end=last;
length mon 3 year&sy-year&ey 8;
array t {12,&sy:&ey} _temporary_;
t[month(dt),year(dt)]+val;
if last then do;
	array y {&sy:&ey} year&sy-year&ey;
	start=0;
	do mon=1 to 12;
		call missing(of y[*]);
		do yr=lbound(t,2) to hbound(t,2);
			if start then y[yr]=(t[mon,yr]-t[mod(mon-13,12)+12,yr-(mon=1)])/t[mon,yr];
			start=1;
		end;
		output;
	end;
end;
keep mon year:;
run;

proc print data=monyr width=min; run;

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

PGStats — Fri, 27 Nov 2020 18:36:12 GMT

@quickbluefish You could also use the TRIMMED option:

proc sql noprint;
select year(min(dt)) into :sy from test;
select year(max(dt)) into :ey from test;
quit;

%let sy=%sysfunc(compress(&sy));
%let ey=%sysfunc(compress(&ey));

proc sql noprint;
select year(min(dt)) into :sy trimmed from test;
select year(max(dt)) into :ey trimmed from test;
quit;

proc sql noprint;
select year(min(dt)), year(max(dt)) into :sy trimmed, :ey trimmed from test;
quit;

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

PaigeMiller — Fri, 27 Nov 2020 18:42:26 GMT

@Jamie_H wrote:

Thank you Paige,

Completely understandable about the virus potential. I tried attached a picture of it first, but the website wont allow that. I've copied the output into a .txt file and attached

Looks like @ballardw has already written the code I was going to write. 😄👍

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

quickbluefish — Fri, 27 Nov 2020 19:03:45 GMT

Thanks! For some reason, I had in my mind that the 'trimmed' option only worked when you were creating a range (like, select x into :x1-x10 trimmed from...) - thanks for the tip!

Re: Summarise data with months down the side, years across the top, and difference from prev mth ins

Jamie_H — Mon, 30 Nov 2020 09:21:05 GMT

Thank you. This works great!