topic Proc Report ACROSS Variable in ODS and Base Reporting

Proc Report ACROSS Variable

OS2Rules — Thu, 23 May 2013 14:02:18 GMT

Hi All:

I currently have a PROC REPORT that prints about 20 variables on an Excel spredsheet using ExcelXP - working great.

My client wants to change the report and add more columns to the right. Since this data is dynamic (I don't know how many columns

will be generated from 1 run to the next), I used an ACROSS variable to print these values.

I can only get this to work when ALL the other variables to the left of the ACROSS variable are 'GROUP' variables (rather than

'DISPLAY' variables as they were before) . Is this usual behavior for the ACROSS usage? Using ORDER only causes each

value of the ACROSS variable to be on a different line of the report.

Using GROUP plays havoc with the data because some of the fields are blank (because they are repeated) - I know how to fix this

but I don't want to do it for all 20 variables.

Any ideas on how this can be fixed?

Thanks in advance.

Re: Proc Report ACROSS Variable

Cynthia_sas — Thu, 23 May 2013 22:02:19 GMT

Hi,

Without seeing data and/or code (most helpful), it is hard to play a guessing game and figure out what you mean. For example, in my mind, there's not a lot of purpose in using ACROSS if you don't also use GROUP for the variables to the left of the ACROSS variable. See the attached program. Neither #1 or #2 report make a lot of sense to me. Clearly I don't understand your data. Perhaps your data or your program is doing something that I can't envision.

For the best help with this, I'd recommend opening a track with SAS Technical Support. They can look at ALL your code and ALL your data and help you come to the best resolution. Otherwise, can you code an example using SASHELP datasets (SASHELP.CARS or SASHELP.HEART)? An example that illustrates your issue/challenge?

cynthia

ods html file='c:\temp\showaccr.html';

proc report data=sashelp.class nowd;

title '1) with ORDER/DISPLAY';

column age name sex,(n height) weight;

define age / order;

define name / display;

define sex / across;

define height/mean 'Weight';

define weight / max 'Max Wt';

run;

proc report data=sashelp.class nowd;

title '2) with ORDER but without NAME';

column age sex,(n height) weight;

define age / order;

define sex / across;

define height/mean 'Weight';

define weight / max 'Max Wt';

run;

proc report data=sashelp.class nowd;

title '3) with GROUP but without NAME';

column age sex,(n height) weight;

define age / group;

define sex / across;

define height/mean 'Weight';

define weight / max 'Max Wt';

run;

ods html close;

Re: Proc Report ACROSS Variable

OS2Rules — Fri, 24 May 2013 12:01:28 GMT

Cyhthia:

I was resisting having to add code, but I knew I would have to in order to be clear.

Here is what I have to run....

proc report data=balance nowd split='*' missing;

column pay_date

action

var1 var2 var3 var4 var5 var6 var7 var8 var9

rept_var, units

;

define pay_date / group 'PAY DATE';

define action / group 'ACTION*TYPE';

define var1 / group 'Var 1';

define var2 / group 'Var 2';

define var3 / group 'Var 3';

define var4 / group 'Var 4';

define var5 / group 'Var 5';

define var6 / group 'Var 6';

define var7 / group 'Var 7';

define var8 / group 'Var 8';

define var9 / group 'Var 9';

define rept_var / across 'Type of Data';

define sum_units / analysis sum 'Units';

run;

There are actually 30+ variables on the report, but you can see the problem here.

My "group" variables are just the PAY_DATE and ACTION. These are also sorted. Unfortunately,

I can't place them to the right on the report next to the ACROSS variable.

If there are 2 values for one of the variables between these 2 variables and the "ACROSS" variable,

the second is left blank on the report (I know how to fix this, but I don't want to do it for all of the

variables - it's a little tedious).

I would just like to display the values of every record for the "VAR##" on the report - which to me

means using a "DISPLAY", not a "GROUP".

If I change any of the DEFINES for the "VAR##" variables to DISPLAY, the ACROSS will not work and

I get each of the across values on it's own line.

Hope I'm explaining the more clearly ....

Re: Proc Report ACROSS Variable

Cynthia_sas — Fri, 24 May 2013 22:33:30 GMT

Hi,

Sorry, it's still clear as "mud" to me. I don't understand why you need to have 9 variables to the left of the ACROSS variables. That's why I asked if you could replicate the report using SASHELP data or with some "fake data". I truly don't understand what you mean when you say "I would just like to display the values of every record for the VAR## on the report" -- to me, this is like my report #1, when you see each NAME on a line by itself, but AGE is the ORDER item on the COLUMN statement (with a usage of DISPLAY, PROC REPORT should complain about GROUP usage.

This is my last try to figure out some fake data. Perhaps someone else can figure it out. Look at the attached screenshot. Still seems to me that #1 is what you're describing. I created duplicate rows for Joyce and John. I changed some values on Joyce's row, but none on John's extra row. I created VAR1-VAR9 for every row. I can understand how you would not like #2, but #1 seems to me to be what you describe. Note that #1 uses ORDER/DISPLAY. Clearly there is something about your data and I don't "intuitively" understand from seeing your code.

cynthia

data makemore;
   array vv $ var1-var9;
   set sashelp.class;
   where name in ('Joyce', 'Thomas', 'John', 'Louise');
   do i = 1 to 9;
      vv(i) = catx('~',i,substr(name,1,2),sex);
   end;
   output;
   ** output some extra observations, 1 dup, 1 diff in var 6;
   if name in ('Joyce' 'John') then do;
     if name = 'Joyce' then var6 = catx('~',6,substr(name,4,2),sex);
     output;
   end;
run;

ods html file='c:\temp\showaccr2.html';
proc report data=makemore nowd;
    title '1) with AGE ORDER and rest of variables DISPLAY';
    column age name var1 var2 var3 var4 var5 var6 var7 var8 var9 sex,(n weight);
    define age / order;
    define name / display;
    define var1 / display;
    define var2 / display;
    define var3 / display;
    define var4 / display;
    define var5 / display;
    define var6 / display;
    define var7 / display;
    define var8 / display;
    define var9 / display;
    define sex / across;
    define weight/mean 'Weight';
   run;

proc report data=makemore nowd;
    title '2) with more variables ORDER/DISPLAY';
    column age name var1 var2 var3 var4 var5 var6 var7 var8 var9 sex,(n weight);
    define age / order;
    define name / order;
    define var1 / order;
    define var2 / order;
    define var3 / order;
    define var4 / order;
    define var5 / order;
    define var6 / order;
    define var7 / order;
    define var8 / order;
    define var9 / order;
    define sex / across;
    define weight/mean 'Weight';
   run;
   ods _all_ close;