Help using Base SAS procedures

FORMAT IN PROC REPORT

Accepted Solution Solved
Reply
Super Contributor
Posts: 276
Accepted Solution

FORMAT IN PROC REPORT

sourcegroupApr'11May'11Jun'11Jul'11
StockNorth1500250035004500

South1500250035004500

East1500250035004500

West1500250035004500
TargetNorth1500250035004500

South1500250035004500

East1500250035004500

West1500250035004500
AchNorth1000100010001000

South700700700700

East850850850850

West925925925925
%AchNorth0.6666670.40.2857140.222222

South0.4666670.280.20.155556

East0.5666670.340.2428570.188889

West0.6166670.370.2642860.205556

Hi

i am created above report by using proc report.

i am trying to give percent10.2 format to Apr'11 may'11,jun'11,jul'11 variables where soure="%ach",but i am not getting the correct logic.

can any one provide solution for this....???

Note:i want to do this in proc report only.


Accepted Solutions
Solution
‎01-24-2012 10:37 PM
SAS Super FREQ
Posts: 8,743

Re: FORMAT IN PROC REPORT

Hi:

  Just curious...in your data and your original post, the value was #Ach (capital A), yet in your IF statement, you use %ach for the comparison (lowercase a). Which one is it, comparisons are case sensitive??? Also, you spelled "group" in the DEFINE statement as GROPU -- is this a typo?

 

  Remember that, by default, PROC REPORT does not display repeating values on every row. So on the first row for each SOURCE, PROC REPORT displays a value for the SOURCE column. But, on subsequent rows, PROC REPORT displays a space for SOURCE. Since it is this displayed value that you are testing, you only know what the FIRST value for SOURCE is. You will probably need to grab the value of SOURCE before the display of the group begins. You can do this easily in a COMPUTE block. The temporary item HOLDSRC, in my code, is a temporary item whose value will be assigned for the first SOURCE of each group, and then automatically retained across all the rows for the same SOURCE value. This means that you will also need to change your IF statements to reference HOLDSRC instead of SOURCE.

  

  In addition, with "%Ach" in double quotes, you are probably seeing this note in the SAS log:

WARNING: Apparent invocation of macro ACH not resolved.

which you can fix by just using '%Ach' -- with single quotes instead.

  The changed code below shows the recommended changes.

cynthia

title; footnote;
ods listing close;
ods html body="C:\temp\use_percent.html";

proc report data=test nowd;
  column source group apr11 may11 jun11 jul11;
  define source/group order=data;
  define group/group order=data;
  define apr11/analysis;
  define may11/analysis;
  define jun11/analysis;
  define jul11/analysis;
  compute before source;
    holdsrc = source;
  endcomp;
  compute apr11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute may11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute jun11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute jul11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
run;

ods html close;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: FORMAT IN PROC REPORT

Hi:

  This is probably beter posted in the ODS and Reporting Forum, but PROC REPORT is a SAS Procedure, so here goes.

  First, what you want to do is very possible using a call define of the form:

CALL DEFINE(_col_,'format','percent10.2');

OR

CALL DEFINE('_Cn_','format','percent10.2');

in a COMPUTE block. You can test the value of other variables in order to accomplish the formatting change. However, the referencing method you use can depend on your code. (are you using ACROSS variables, are you using simple variables in the COLUMN statement?)

  So, can you post your PROC REPORT code, including ODS statements? Also useful would be a small sample of the data or some fake data...

cynthia

Super Contributor
Posts: 276

Re: FORMAT IN PROC REPORT

Hi..

please find the bellow data and code....

data test;

input source $    group$    Apr11    May11    Jun11    Jul11;

cards;

Stock    North    1500    2500    3500    4500

Stock    South    1500    2500    3500    4500

Stock    East    1500    2500    3500    4500

Stock    West    1500    2500    3500    4500

Target    North    1500    2500    3500    4500

Target    South    1500    2500    3500    4500

Target    East    1500    2500    3500    4500

Target    West    1500    2500    3500    4500

Ach    North    1000    1000    1000    1000

Ach    South    700        700        700        700

Ach    East    850        850        850        850

Ach    West    925        925        925        925

%Ach    North    0.666667    0.4    0.285714    0.222222

%Ach    South    0.466667    0.28    0.2    0.155556

%Ach    East    0.566667    0.34    0.242857    0.188889

%Ach    West    0.616667    0.37    0.264286    0.205556

;

run;

ods html body="C:\Users\sanjeev\Desktop\file.html";

proc report data=test;

column source group apr11 may11 jun11 jul11;

define source/group;

define gropu/group;

define apr11/analysis;

define may11/analysis;

define jun11/analysis;

define jul11/analysis;

compute apr11;

if source="%ach" then do;

call define(_col_,'format','percent10.2');

end;

endcomp;

compute may11;

if source="%ach" then do;

call define(_col_,'format','percent10.2');

end;

endcomp;

compute jun11;

if source="%ach" then do;

call define(_col_,'format','percent10.2');

end;

endcomp;

compute jul11;

if source="%ach" then do;

call define(_col_,'format','percent10.2');

end;

endcomp;

run;

ods html close;

Solution
‎01-24-2012 10:37 PM
SAS Super FREQ
Posts: 8,743

Re: FORMAT IN PROC REPORT

Hi:

  Just curious...in your data and your original post, the value was #Ach (capital A), yet in your IF statement, you use %ach for the comparison (lowercase a). Which one is it, comparisons are case sensitive??? Also, you spelled "group" in the DEFINE statement as GROPU -- is this a typo?

 

  Remember that, by default, PROC REPORT does not display repeating values on every row. So on the first row for each SOURCE, PROC REPORT displays a value for the SOURCE column. But, on subsequent rows, PROC REPORT displays a space for SOURCE. Since it is this displayed value that you are testing, you only know what the FIRST value for SOURCE is. You will probably need to grab the value of SOURCE before the display of the group begins. You can do this easily in a COMPUTE block. The temporary item HOLDSRC, in my code, is a temporary item whose value will be assigned for the first SOURCE of each group, and then automatically retained across all the rows for the same SOURCE value. This means that you will also need to change your IF statements to reference HOLDSRC instead of SOURCE.

  

  In addition, with "%Ach" in double quotes, you are probably seeing this note in the SAS log:

WARNING: Apparent invocation of macro ACH not resolved.

which you can fix by just using '%Ach' -- with single quotes instead.

  The changed code below shows the recommended changes.

cynthia

title; footnote;
ods listing close;
ods html body="C:\temp\use_percent.html";

proc report data=test nowd;
  column source group apr11 may11 jun11 jul11;
  define source/group order=data;
  define group/group order=data;
  define apr11/analysis;
  define may11/analysis;
  define jun11/analysis;
  define jul11/analysis;
  compute before source;
    holdsrc = source;
  endcomp;
  compute apr11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute may11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute jun11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
  compute jul11;
    if holdsrc='%Ach' then do;
      call define(_col_,'format','percent10.2');
    end;
  endcomp;
run;

ods html close;

Super Contributor
Posts: 276

Re: FORMAT IN PROC REPORT

Thank you...its working...

one more thing..

if the value of apr or may or jun in %ach is less then 50%, i want to blink that values...

is it possible???

SAS Super FREQ
Posts: 8,743

Re: FORMAT IN PROC REPORT

Hi:

If you want to "blank" the values ( change the value from a number to having a space displayed) when the value meets or doesn't meet a condition, you can do that with more IF logic in your COMPUTE block. You will have to remember 2 things:

1) missing values for numeric items are a period or dot . -- so if you want to assign the value of missing to a numeric column, you will need this option to display missing as blank:

options missing=' ';

and

2) when you use ANALYSIS as the usage for a column, the default statistic is SUM. So, in a COMPUTE block, you cannot reference the simple item or column name (such as APR11) , you would have to use the "compound" name that PROC REPORT expects for an ANALYSIS item. For a column like APR11, with a usage of ANALYSIS and the SUM statistic as the default, the compound name would be APR11.SUM (There are other rules for referencing, such as if you have ACROSS items -- but you don't, so I won't mention them here.) Do note, that if you had a different statistic on the DEFINE statement for AGE (such as MEAN, for example), then the compound name would be different (such as AGE.MEAN). If you do not use the correct compound name in your COMPUTE block, you will get a message about an unitialized variable in the SAS log.

  The code below using SASHELP.CLASS, changes Alfred's height to 999. It also changes Barbara's age to missing and Alice's weight to missing. Then because of the OPTIONS statement, the missing is displayed as blank in the output.

cynthia

options missing = ' ';

ods listing close;

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

     

proc report data=sashelp.class nowd;

  column name age sex height weight;

  define name / order;

  define age / analysis;

  define sex / display;

  define height / sum analysis;

  define weight / sum;

  compute age;

     if name = 'Barbara' then do;

        age.sum = .;

     end;

  endcomp;

  compute height;

     if name = 'Alfred' then do;

        height.sum = 999;

     end;

  endcomp;

  compute weight;

     if name = 'Alice' then do;

        weight.sum = .;

     end;

  endcomp;

run;

ods html close;

options missing = .;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1343 views
  • 1 like
  • 2 in conversation