topic Re: FORMAT IN PROC REPORT in SAS Procedures

FORMAT IN PROC REPORT

kuridisanjeev — Tue, 24 Jan 2012 17:31:40 GMT

source	group	Apr'11	May'11	Jun'11	Jul'11
Stock	North	1500	2500	3500	4500
	South	1500	2500	3500	4500
	East	1500	2500	3500	4500
	West	1500	2500	3500	4500
Target	North	1500	2500	3500	4500
	South	1500	2500	3500	4500
	East	1500	2500	3500	4500
	West	1500	2500	3500	4500
Ach	North	1000	1000	1000	1000
	South	700	700	700	700
	East	850	850	850	850
	West	925	925	925	925
%Ach	North	0.666667	0.4	0.285714	0.222222
	South	0.466667	0.28	0.2	0.155556
	East	0.566667	0.34	0.242857	0.188889
	West	0.616667	0.37	0.264286	0.205556

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.

Re: FORMAT IN PROC REPORT

Cynthia_sas — Tue, 24 Jan 2012 19:29:56 GMT

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');

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

Re: FORMAT IN PROC REPORT

kuridisanjeev — Wed, 25 Jan 2012 02:00:22 GMT

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;

Re: FORMAT IN PROC REPORT

Cynthia_sas — Wed, 25 Jan 2012 03:37:02 GMT

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;

Re: FORMAT IN PROC REPORT

kuridisanjeev — Thu, 26 Jan 2012 03:47:52 GMT

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???

Re: FORMAT IN PROC REPORT

Cynthia_sas — Thu, 26 Jan 2012 14:54:25 GMT

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 = .;