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 😊
... View more