I feel like we have given you this advice a gazillion and twelve times.
Maxim 19
Long beats wide.
(Don't keep data in structure)
In the world of spreadsheets, people tend to line up data side-by-side, and put data items (dates, categories, …) into column headers. This runs counter to all the methods available in SAS for group processing, and makes programming difficult, as one has variable column names and has to resort to creating dynamic code (with macros and/or call execute) where such is not necessary at all if categories were represented in their own column and data aligned vertically. There are times where a wide format is needed, eg when preparing data for regression analysis. But for the processing and storing of data, long formats are always to be preferred.
Dynamic variable names force unnecessary dynamic code.
Code:
data long;
set have1;
array w wealth:;
array i ind:;
do j=1 to dim(w);
yrmonth=input(substr(vname(w(j)),7),yymmn4.);
wealth=w(j);
ind=i(j);
output;
end;
keep custid wealth ind yrmonth;
run;
proc format;
value redf 1=red other=white;
run;
proc report data=long;
columns custid wealth,yrmonth ind,yrmonth;
define custid/group;
define yrmonth/across format=monyy. order=internal ' ';
define ind/mean;
compute ind;
call define(_col_,"Style","style={background=redf.}");
endcompute;
run;
No dynamic code needed to process the different year/month combinations. Just arrange your data in the proper format, which is the LONG format. Then SAS has features that do the hard work for you, so you don't have to code it yourself (just like Maxim 19 says). And now the code works regardless of the year/month comibnations in your data.
... View more
