replace '.' with 0

[France]

hello all,

I would like to replace '.' with 0 in the following dataset.

except for the first column, the rest is the number of application per month during the period between 2005 and 2015, namely, from 'application200501' to 'application201512'.

I have tried to use array statement which is failed. Could you please give me some suggestion?

thanks in advance.

best regards,

France

[Quentin]

Please show the code you tried with the array statement, and describe how it failed.  Did you get an error?  Did you get an unexpected result?

[France]

Dear Quentin

Thanks for your advise.  I firstly try to merge each data set and  replace '.' with '0' by following codes,

DATA Step4.number_of_patent_end ;
  MERGE Step3.Number_of_appln_pat2005 - Step3.Number_of_appln_pat2015 ;
  BY psn_name;
RUN;


DATA Step5.ar_number_of_patent_end ;
 SET Step4.number_of_patent_end ;
 ARRAY application(132) application200501 - application201512 ;
 DO i= 1 TO 132;
 IF application(i) eq . THEN application(i) = 0 ;
 END;
RUN;

99 DATA Step5.ar_number_of_patent_end ;
100 SET Step4.number_of_patent_end ;
101 ARRAY application(132) application200501 - application201512 ;
ERROR: Too many variables defined for the dimension(s) specified for the array application.
102 DO i= 1 TO 132;
103 IF application(i) eq . THEN application(i) = 0 ;
104 END;
105 RUN;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
 103:5
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set STEP5.AR_NUMBER_OF_PATENT_END may be incomplete. When this step was stopped
 there were 0 observations and 134 variables.
NOTE: DATA statement used (Total process time):
 real time 0.72 seconds
 cpu time 0.04 seconds

so I would like to replace '.' with '0' for each data set and then merge each data set by following codes

%macro array_pat;
   %local q;
   %do q=2005 %to 2015;

DATA Step4.ar_number_of_appln_pat&q ;
    SET Step3.number_of_appln_pat&q ;
    ARRAY application(12) application&q.01 - application&q.12 ;
    DO i= 1 TO 12;
    IF application(i) eq . THEN application(i) = 0 ;
    END;
RUN;

%mend array_pat;
 
%array_pat

, but the result shows that,

84   %macro array_pat;
85      %local q;
86      %do q=2005 %to 2015;
87
88   DATA Step4.ar_number_of_appln_pat&q ;
89       SET Step3.number_of_appln_pat&q ;
90       ARRAY application(12) application&q.01 - application&q.12 ;
91       DO i= 1 TO 12;
92       IF application(i) eq . THEN application(i) = 0 ;
93       END;
94   RUN;
95
96   %mend array_pat;
ERROR: There were 1 unclosed %DO statements.  The macro ARRAY_PAT will not be compiled.
ERROR: A dummy macro will be compiled.
97
98   %array_pat
     -
     180
WARNING: Apparent invocation of macro ARRAY_PAT not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Could you give me some suggestion about this please ?

thanks in advance,

best regards,

France

[Tom]

The error message is very clear. You have a %DO but no matching %END.

[VDD]

I think what you have

%array_pat

needs to have a semicolon

%array_pat;

[France]

Dear VDD,

thanks for your suggestion,

by using the codes,

%macro array_pat;
   %local q;
   %do q=2005 %to 2015;

DATA Step4.ar_number_of_appln_pat&q ;
    SET Step3.number_of_appln_pat&q ; 
    ARRAY application(12) application&q.01 - application&q.12 ;
    DO i= 1 TO 12;
    IF application(i) eq . THEN application(i) = 0 ;
    %END;
RUN;

%mend array_pat;
 
%array_pat；

the result shows that 

NOTE: Line generated by the invoked macro "ARRAY_PAT".
21     DATA Step4.ar_number_of_appln_pat&q ;     SET Step3.number_of_appln_pat&q ;     ARRAY
      -
      117
21 ! application(12) application&q.01 - application&q.12 ;     DO i= 1 TO 12;     IF application(i) eq
21 !  . THEN application(i) = 0 ;

ERROR 117-185: There was 1 unclosed DO block.

NOTE: Line generated by the invoked macro "ARRAY_PAT".
23    RUN;
         -
         117
ERROR 117-185: There was 1 unclosed DO block.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set STEP4.AR_NUMBER_OF_APPLN_PAT2015 may be incomplete.  When this step was stopped
         there were 0 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Could you please give me some advice for it?

thanks in advance

[Kurt_Bremser · Accepted solution]

A do needs an end, and a %do needs a %end.

[France]

the final code is

%macro array_pat;
   %local q;
   %do q=2005 %to 2015;

DATA Step4.ar_number_of_appln_pat&q ;
    SET Step3.number_of_appln_pat&q ; 
    ARRAY application(12) application&q.01 - application&q.12 ;
    DO i= 1 TO 12;
    IF application(i) eq . THEN application(i) = 0 ;
	END;
    DROP i;

   %END;
RUN;

%mend array_pat;
 
%array_pat；

thank you all.

[Tom]

Your last data step will be missing the RUN statement because of misaligned loops.  I find it helps to treat the macro code and regular code separately when indenting the code to help keep the separation clearer.

%macro array_pat;
%local q;
%do q=2005 %to 2015;

DATA Step4.ar_number_of_appln_pat&q ;
    SET Step3.number_of_appln_pat&q ; 
    ARRAY application(12) application&q.01 - application&q.12 ;
    DO i= 1 TO 12;
        IF application(i) eq . THEN application(i) = 0 ;
    END;
    DROP i;
RUN;

%end;
%mend array_pat;

[France]

Dear Tom, thanks for your suggestion

[VDD]

put this before your data step or at the top of your program.

options missing = 0;

[Quentin]

---

@VDD wrote:

put this before your data step or at the top of your program.

options missing = 0;

---

That will change how missing values are displayed, but will not recode the actual value.  The value stored in the data would still be missing. e.g.:

1    options missing=0 ;
2
3    data a ;
4      x=. ;
5      y=x+5 ;
6      put (x y)(=) ;
7    run ;

x=0 y=0
NOTE: Missing values were generated as a result of performing an operation on missing
      values.

[VDD]

Here is how to fix that:

options missing=0;

data a;

y=5;

x=.;

y+x ;

put (x y)(=) ;

run

[VDD]

1 options missing=0;

2 data a;

3 y=5;

4 x=.;

5 y+x ;

6

7 put (x y )(=) ;

8 x+y-4;

9 put (x) (=);

10 run ;

x=0 y=5

x=1

[Kurt_Bremser]

Do this:

proc transpose
  data=have
  out=want (
    compress=yes
    rename=(
      col1=applications
      _name_=period
    )
    where=(applications ne .)
  )
;
copy psn_name;
var application:;
run;

You will find that normalized tables are much easier to work with, and you don't waste space for null values.