Hello,
You can find a doc where i get missing values in '.' or 'i want '.' instead. What do I have to do please?
Thanks
You can use PROC STDIZE (for "standardize") to convert all missing values ('.') to zero.
proc stdize
data=WORK.QUERY_FOR_QUERY_CREES_CHALONS_CA
out=work.now_zeros
reponly
missing=0;
run;
Then use work.now_zeros in your PROC REPORT.
More in this solved discussion.
The string that is displayed for a numeric missing value can be changed through the system option missing=, like
options missing='0';
If you want to actually change your missing numeric values to 0, do
data test;
set test;
array _nums {*} _numeric_;
do _i = 1 to dim(_nums);
if _nums{_i} = . then _nums{_i} = 0;
end;
drop _i;
run;
PS I (and most others here) willl not (or even cannot) open a MS Office document from the web, for security reasons. Post program code as text, using the {i} or "little running man" icons.
Thnak you kurt :
1) sorry for the document
2) how do i do this?
The string that is displayed for a numeric missing value can be changed through the system option missing=, like
options missing='0';
3) where do I put this ? in the program code that ii send you? My level is very low in program
data test;
set test;
array _nums {*} _numeric_;
do _i = 1 to dim(_nums);
if _nums{_i} = . then _nums{_i} = 0;
end;
drop _i;
run;
Post your program code, and I'll be able to show you.
TITLE1 "Rapport sous forme de liste";
FOOTNOTE1 "Généré par le Système SAS (&_SASSERVERNAME, &SYSSCPL) le %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) à %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
proc report data=WORK.QUERY_FOR_QUERY_CREES_CHALONS_CA nowd;
column 'Libellé canal physique'n 'Créés'n, SUM='Créés_SUM'n 'Traités'n, SUM='Traités_SUM'n 'Taux de traitement'n, SUM='Taux de traitement_SUM'n Abandons, SUM=Abandons_SUM 'Taux d''abandon'n, SUM='Taux d''abandon_SUM'n 'A faire'n, SUM='A faire_SUM'n Stocks, SUM=Stocks_SUM Instances, SUM=Instances_SUM 'Total de A TRAITER'n, SUM='Total de A TRAITER_SUM'n;
define 'Libellé canal physique'n / group 'Libellé canal physique' format=$CHAR17. missing order=formatted;
compute 'Libellé canal physique'n;
if 'Libellé canal physique'n ne ' ' then hold1='Libellé canal physique'n;
if 'Libellé canal physique'n eq ' ' then 'Libellé canal physique'n=hold1;
endcomp;
define 'Créés'n / analysis SUM 'Créés' missing;
define 'Traités'n / analysis SUM 'Traités' missing;
define 'Taux de traitement'n / analysis SUM 'Taux de traitement' format=NLPCTI6. missing;
define 'Taux de traitement_SUM'n / format=NLPCTI6.;
define Abandons / analysis SUM 'Abandons' missing;
define 'Taux d''abandon'n / analysis SUM "Taux d'abandon" format=NLPCTI6. missing;
define 'Taux d''abandon_SUM'n / format=NLPCTI6.;
define 'A faire'n / analysis SUM 'A faire' missing;
define Stocks / analysis SUM 'Stocks' missing;
define Instances / analysis SUM 'Instances' missing;
define 'Total de A TRAITER'n / analysis SUM 'Total de A TRAITER' format=NLNUM8. missing;
define 'Total de A TRAITER_SUM'n / format=NLNUM8.;
run;
quit;
TITLE; FOOTNOTE;
For Kuurt thank you
It's a simple run of one procedure, so add my code right before this. In the case of the data step replace "test" with your
WORK.QUERY_FOR_QUERY_CREES_CHALONS_CA
or use @ChrisHemedinger's example on your dataset.
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_QUERY_CREES_CHALONS_CA(label="QUERY_FOR_QUERY_CREES_CHALONS_CANAUX") AS
SELECT t1.'Libellé canal physique'n,
t1.'COUNT_of_Libellé canal physique'n AS 'Créés'n,
t4.'COUNT_of_Libellé canal physique'n AS 'Traités'n,
/* Taux de traitement */
(t4.'COUNT_of_Libellé canal physique'n/t1.'COUNT_of_Libellé canal physique'n) FORMAT=NLPCTI6. AS
'Taux de traitement'n,
t3.'COUNT_of_Libellé canal physique'n AS Abandons,
/* Taux d'abandon */
(t3.'COUNT_of_Libellé canal physique'n/t1.'COUNT_of_Libellé canal physique'n) FORMAT=NLPCTI6. AS
'Taux d''abandon'n,
t2.'COUNT_of_Libellé canal physique'n FORMAT=BEST11. AS 'A faire'n,
t5.'COUNT_of_Libellé canal physique'n AS Stocks,
t6.'COUNT_of_Libellé canal physique'n AS Instances,
/* Total de A TRAITER */
(t2.'COUNT_of_Libellé canal physique'n+t5.'COUNT_of_Libellé canal physique'n+t6.
'COUNT_of_Libellé canal physique'n) FORMAT=NLNUM8. AS 'Total de A TRAITER'n
FROM WORK.QUERY_CREES_CHALONS_CANAUX t1
LEFT JOIN WORK.QUERY_AFAIRE_CANAUX_CHALONS t2 ON (t1.'Libellé canal physique'n = t2.'Libellé canal physique'n
)
INNER JOIN WORK.QUERY_ABANDON_CANAUX_CHALONS t3 ON (t1.'Libellé canal physique'n = t3.
'Libellé canal physique'n)
INNER JOIN WORK.QUERY_TRAITES_CANAUX_CHALONS t4 ON (t1.'Libellé canal physique'n = t4.
'Libellé canal physique'n)
INNER JOIN WORK.QUERY_STOCK_CANAUX_CHALONS t5 ON (t1.'Libellé canal physique'n = t5.'Libellé canal physique'n
)
LEFT JOIN WORK.QUERY_INSTANCE_CHALONS_CANAUX t6 ON (t1.'Libellé canal physique'n = t6.
'Libellé canal physique'n);
options missing='0';
QUIT;
@Kurt_Bremser, I put the "option missing" and it gives me 0 in the colum A faire. But it gives me 0 in the column "Total de A Traiter" which is the sum of "A faire" + "Stcok"...
I try the program from @ChrisHemedinger and it's OK the sum "Total a traiter" is OK.
Thank you a lot to both of you for the next calcul i will use Chris code!
And sorry for my level of english and sas programmation..I'm just a franch novice:)
options missing just changes the display of missing values. @ChrisHemedinger's example and my data step change the actual values in the dataset.
Now, when only the display is changed, the values are still missing values, and a basic mathematical operation that involves one of those values will result in another missing value, even when the other variables are non-missing.
Once the values are changed to zero, all calculations start to work.
You can use PROC STDIZE (for "standardize") to convert all missing values ('.') to zero.
proc stdize
data=WORK.QUERY_FOR_QUERY_CREES_CHALONS_CA
out=work.now_zeros
reponly
missing=0;
run;
Then use work.now_zeros in your PROC REPORT.
More in this solved discussion.
Thank you Chris, but it's the same problem that with the Kurt solution..where do I put it in the program? sorry:)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.