PROC REPORT-Display _ALL_ columns and color columns start with name IND

[Ronein]

Hello

I want to use proc report to  do the following-

1- Display all columns (I dont want to write the columsn names and I just want to tell SAS to dispaly all columns in data set)

2-COLOR COLUMNS with initial name "Ind"  to red if the value is 1 

Please note that the code should be flexible,which means that number of columns in data set can be different .

data have1;
input CustID Wealth2501 wealth2502 wealth2503  Ind2501 ind2502 ind2503;
cards;
1 10 20 30 0 0 0
2 30 20 10 0 1 1
3 50 40 20 0 1 1
;
Run;


data have2;
input CustID Wealth2403 wealth2403   Ind2403 ind2403 ;
cards;
1 70 80 0 0 
2 60 80 0 0 
3 80 40 0 1 
;
Run;


proc report data=have1;
column _All_;
run;


proc report data=have2;
column _All_;
run;

[PaigeMiller · Accepted solution]

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.

[Kathryn_SAS]

You can also use macro logic to do this. Since your variables are numeric, it helps to define one as DISPLAY so that you get a row for every observation. Also in the Compute block, you must use the .sum suffix on the numeric variables if you are not defining them with another usage.

data have1;
input CustID Wealth2501 wealth2502 wealth2503  Ind2501 ind2502 ind2503;
cards;
1 10 20 30 1 0 0
2 30 20 10 0 1 1
3 50 40 20 0 1 1
;
Run;

proc sql noprint;
 select name into :varlist separated by ' '
 from dictionary.columns
 where libname='WORK' and memname='HAVE1' and upcase(name) like 'IND%';
quit;
%put &varlist;
%let count=&sqlobs;
%put &count;

%macro loop;
ods listing close;
ods excel file='test.xlsx';

proc report data=have1;
column _All_ dummy;
define custid / display;
define dummy / computed noprint;
compute dummy;
 %do i=1 %to &count;
  if %scan(&varlist,&i,%str( )).sum=1 then call define("%scan(&varlist,&i,%str( )).sum",
 'style','style=[background=red]');
 %end;
endcomp; 
run;

ods excel close;
ods listing;
%mend;
options mprint mlogic symbolgen;
%loop

[Ronein]

Perfect!!! Thank you so much.

2 questions please:

I removed the code : define ind/mean;   Is this code essential? I see there is no difference in the result.

Why didnt you also add the code :define wealth/mean; ?

I removed ' '  after order=internal and I see same results.

Is this code essenstail? What is the purpose of  ' ' after order=internal?

proc report data=long;
    columns custid wealth,yrmonth ind,yrmonth;
    define custid/group;
    define yrmonth/across format=monyy. order=internal;
    compute ind;
        call define(_col_,"Style","style={background=redf.}");
    endcompute;
run;

[PaigeMiller]

The default statistic is the mean, if you remove the DEFINE statement for IND it produces a mean. So in this case, you don't really need the DEFINE statement. That's also why the results for WEALTH also work. 

If you remove the ' ' from after ORDER=INTERNAL there is a difference, which you should see.

[Kathryn_SAS]

Just to clarify, the default statistic for analysis variables in PROC REPORT is Sum.

[PaigeMiller]

Yes, thanks, in this case it doesn't matter because the sum of a number is equal to the mean of that number.

[Ronein]

I see, It remove the title (using ' ')

[Ksharp]

I  also would like to use macro file.

data have1;
input CustID Wealth2501 wealth2502 wealth2503  Ind2501 ind2502 ind2503;
cards;
1 10 20 30 0 0 0
2 30 20 10 0 1 1
3 50 40 20 0 1 1
;
Run;

proc transpose data=have1(obs=0) out=temp;
var _all_;
run;
filename x temp;
data _null_;
 set temp(where=(upcase(_name_) =: 'IND'));
 file x;
 put 'compute ' _name_ ';';
 put 'if ' _name_ '=1 then call define(_col_,"style","style={background=red}");';
 put 'endcomp;';
run;


proc report data=have1 nowd;
column _All_ ;
define _all_/display;
%include x/source source2;
run;