Proc report : cannot get value from the last _c_ column.

[v2rt]

Hi all,

After having searched for a long time on the internet for a similar problem I unfortunately did not find any solution or explanation to my problem. So I'm writing this post in order to try to solve it. I am using SAS 8.2 EG.

My goal is to calculate the variability (Variation) of the number of contracts (N) across different dates (Date_export) for different Companies (Compagnie). Here is a sample of my data set :

DATA Contract; 
   LENGTH Compagnie $10.;
   INPUT Compagnie $ Date_export $ ;
CARDS;
FORD 2020/01
FORD 2020/02
FORD 2020/02
FORD 2020/03
FORD 2020/03
FORD 2020/03
BMW 2020/01
BMW 2020/02
BMW 2020/02
BMW 2020/02
BMW 2020/03
BMW 2020/03
RENAULT 2020/01
RENAULT 2020/01
RENAULT 2020/01
RENAULT 2020/02
RENAULT 2020/02
RENAULT 2020/03
;
RUN;

To solve this problem I am using a PROC REPORT with a Computed value for Variation :

PROC REPORT DATA=Contract SPLIT='00'x style(summary)=[color=cx3e3d73 backgroundcolor=BWH] ;
COLUMN Compagnie  Date_export , (Variation N)  ;
DEFINE Compagnie / GROUP MISSING ORDER=INTERNAL style(Column)=[fontweight=bold backgroundcolor=Honeydew];
DEFINE Date_export / ACROSS ORDER=INTERNAL  '';
DEFINE N / "N";
DEFINE Variation / COMPUTED format=percent8.2 ;
compute Variation;

	_c4_ = ((_c5_ - _c3_)/(_c3_));
	_c6_ = ((_c7_ - _c5_)/(_c5_));
if _c4_ >0 then call define ('_c4_','style','style={color=green}'); else call define ('_c4_','style','style={color=red}');
if _c6_ >0 then call define ('_c6_','style','style={color=green}'); else call define ('_c6_','style','style={color=red}');
endcomp;
RBREAK  AFTER  /SUMMARIZE;
RUN;

And here is the result of this program:

As you can see, the Variation column for the date 2020/03 is not completed even though I coded it ( _c6_ = ((_c7_ - _c5_)/(_c5_)); ). After making some changes, I realized that I could not get the data from the _c7_ column (the last column). Because if I do _c6_ = _c3_ for example, my column fills up as it should. So I think the problem would come from the last column _c7_.

What is even more surprising is that when I switch N and Variation in the COLUMN line of the PROC REPORT and change the formulas of the corresponding columns, as below :

PROC REPORT DATA=Contract SPLIT='00'x style(summary)=[color=cx3e3d73 backgroundcolor=BWH] ;
COLUMN Compagnie  Date_export , (N Variation)  ;
DEFINE Compagnie / GROUP MISSING ORDER=INTERNAL style(Column)=[fontweight=bold backgroundcolor=Honeydew];
DEFINE Date_export / ACROSS ORDER=INTERNAL  '';
DEFINE N / "N";
DEFINE Variation / COMPUTED format=percent8.2 ;
compute Variation;

	_c5_ = ((_c4_ - _c2_)/(_c2_));
	_c7_ = ((_c6_ - _c4_)/(_c4_));
if _c5_ >0 then call define ('_c5_','style','style={color=green}'); else call define ('_c5_','style','style={color=red}');
if _c7_ >0 then call define ('_c7_','style','style={color=green}'); else call define ('_c7_','style','style={color=red}');
endcomp;
RBREAK  AFTER  /SUMMARIZE;
RUN;

I then get this result:

which is the right result but it isn't the shape of the table I want

Indeed, it is important for me to have the Variation before the N for each Export_Date as in the first example.

Thank you by advance for your help 😊

[Sajid01]

It appears the last columns is not being read.
To test it I added one row to your dataset as shown below. I wish somebody could enlighten us.

DATA Contract; 
   LENGTH Compagnie $10.;
   INPUT Compagnie $ Date_export $ ;
CARDS;
FORD 2020/01
FORD 2020/02
FORD 2020/02
FORD 2020/03
FORD 2020/03
FORD 2020/03
BMW 2020/01
BMW 2020/02
BMW 2020/02
BMW 2020/02
BMW 2020/03
BMW 2020/03
RENAULT 2020/01
RENAULT 2020/01
RENAULT 2020/01
RENAULT 2020/02
RENAULT 2020/02
RENAULT 2020/03
ZZ       2020/04
;
RUN;

PROC REPORT DATA=Contract SPLIT='00'x style(summary)=[color=cx3e3d73 backgroundcolor=BWH] ;
COLUMN Compagnie  Date_export , (Variation N )  ;
DEFINE Compagnie / GROUP MISSING ORDER=INTERNAL style(Column)=[fontweight=bold backgroundcolor=Honeydew];
DEFINE Date_export / ACROSS ORDER=INTERNAL  '';
DEFINE N / " N ";
DEFINE Variation / COMPUTED format=percent12.2 ;
compute Variation;
	_C4_ = ((_C5_ - _C3_)/(_C3_));
	_C6_ =((_C7_ -_C5_)/(_C5_));
if _C4_ >0 then call define ('_c4_','style','style={color=green}'); else call define ('_c4_','style','style={color=red}');
if _C6_ >0 then call define ('_c6_','style','style={color=green}'); else call define ('_c6_','style','style={color=red}');
endcomp;
RBREAK  AFTER  /SUMMARIZE;
RUN;

The out put was

[v2rt]

Thanks Sajid01 for your answer !

Indeed, it seems that we can't get values of the last column...

I hope someone will be able to give us a technical explanation for this problem.

[Kurt_Bremser · Accepted solution]

In a compute block, you can only use variables that are to the left in the COLUMN statement. If you want a variable to show up before the variable that is used to compute it, you must create a "hidden" copy which you can use in the calculation:

proc report
  data=Contract
  split='00'x
  style(summary)=[color=cx3e3d73 backgroundcolor=BWH]
;
column Compagnie Date_export,(n=n1 Variation N);
define Compagnie / group missing order=INTERNAL style(Column)=[fontweight=bold backgroundcolor=Honeydew];
define Date_export / across order=INTERNAL  '';
define n1 / noprint;
define Variation / computed format=percent8.2 ;
define N / "N";
compute Variation;
  _c6_ = ((_c5_ - _c2_)/(_c2_));
  _c9_ = ((_c8_ - _c5_)/(_c5_));
  if _c6_ >0 then call define ('_c6_','style','style={color=green}'); else call define ('_c6_','style','style={color=red}');
  if _c9_ >0 then call define ('_c9_','style','style={color=green}'); else call define ('_c9_','style','style={color=red}');
endcomp;
rbreak after / summarize;
run;

[v2rt]

Thank you very much Kurt_Bremser for the explanation and the solution !

It works perfectly for me !

Have a nice day 😊

[FreelanceReinh]

Hi @v2rt,

Alternatively, you can perform your computations in a COMPUTE block of such a hidden ("dummy") column whose position in the COLUMN statement is to the right of the columns used in the COMPUTE block:

PROC REPORT DATA=Contract SPLIT='00'x style(summary)=[color=cx3e3d73 backgroundcolor=BWH] ;
COLUMN Compagnie  Date_export , (Variation N) dummy;
DEFINE Compagnie / GROUP MISSING ORDER=INTERNAL style(Column)=[fontweight=bold backgroundcolor=Honeydew];
DEFINE Date_export / ACROSS ORDER=INTERNAL  '';
DEFINE N / "N";
DEFINE Variation / COMPUTED format=percent8.2 ;
DEFINE dummy / computed noprint;
compute dummy;
	_c4_ = ((_c5_ - _c3_)/(_c3_));
	_c6_ = ((_c7_ - _c5_)/(_c5_));
if _c4_ >0 then call define ('_c4_','style','style={color=green}'); else call define ('_c4_','style','style={color=red}');
if _c6_ >0 then call define ('_c6_','style','style={color=green}'); else call define ('_c6_','style','style={color=red}');
endcomp;
RBREAK  AFTER  /SUMMARIZE;
RUN;

This trick is presented in section 8.4.4 Using a Dummy Column to Consolidate Compute Blocks (p. 283 f.) of Carpenter's Guide to Innovative SAS Techniques (see https://support.sas.com/en/books/authors/art-carpenter.html).

[v2rt]

Hi @FreelanceReinh,

Yes, it works too! It's maybe even cleaner because you don't have to skip "invisible" columns when you do the calculations in the compute.

Thanks for the explanation of this tricks and for the reference!