topic Re: Transform . to 0 in SAS Enterprise Guide

Transform . to 0

peter2 — Mon, 07 Nov 2016 11:35:39 GMT

Hello,

You can find a doc where i get missing values in '.' or 'i want '.' instead. What do I have to do please?

Thanks

Re: Transform . to 0

Kurt_Bremser — Mon, 07 Nov 2016 11:44:31 GMT

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.

Re: Transform . to 0

peter2 — Mon, 07 Nov 2016 11:58:25 GMT

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;

Re: Transform . to 0

Kurt_Bremser — Mon, 07 Nov 2016 12:01:11 GMT

Post your program code, and I'll be able to show you.

Re: Transform . to 0

ChrisHemedinger — Mon, 07 Nov 2016 12:38:31 GMT

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.

Re: Transform . to 0

peter2 — Mon, 07 Nov 2016 12:46:25 GMT


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

Re: Transform . to 0

peter2 — Mon, 07 Nov 2016 12:49:51 GMT

Thank you Chris, but it's the same problem that with the Kurt solution..where do I put it in the program? sorry:)

Re: Transform . to 0

Kurt_Bremser — Mon, 07 Nov 2016 12:51:57 GMT

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.

Re: Transform . to 0

peter2 — Mon, 07 Nov 2016 13:17:33 GMT

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:)

Re: Transform . to 0

Kurt_Bremser — Mon, 07 Nov 2016 13:26:56 GMT

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.