BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
peter2
Obsidian | Level 7

Hello,

 

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

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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.

peter2
Obsidian | Level 7

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;

peter2
Obsidian | Level 7

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

 

Kurt_Bremser
Super User

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.

peter2
Obsidian | Level 7
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:)

Kurt_Bremser
Super User

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.

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
peter2
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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