Desktop productivity for business analysts and programmers

Transform . to 0

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Transform . to 0

Hello,

 

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

Thanks


Accepted Solutions
Solution
‎11-07-2016 08:17 AM
Community Manager
Posts: 2,889

Re: Transform . to 0

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.

View solution in original post


All Replies
Super User
Posts: 7,422

Re: Transform . to 0

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

Re: Transform . to 0

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;

Super User
Posts: 7,422

Re: Transform . to 0

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

Re: Transform . to 0


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

 

Super User
Posts: 7,422

Re: Transform . to 0

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

Re: Transform . to 0

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;


@KurtBremser, 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 noviceSmiley Happy

Super User
Posts: 7,422

Re: Transform . to 0

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-07-2016 08:17 AM
Community Manager
Posts: 2,889

Re: Transform . to 0

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.

Contributor
Posts: 31

Re: Transform . to 0

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 338 views
  • 1 like
  • 3 in conversation