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
A do needs an end, and a %do needs a %end.
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?
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
The error message is very clear. You have a %DO but no matching %END.
I think what you have
%array_pat
needs to have a semicolon
%array_pat;
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
A do needs an end, and a %do needs a %end.
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.
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;
put this before your data step or at the top of your program.
options missing = 0;
@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.
Here is how to fix that:
options missing=0;
data a;
y=5;
x=.;
y+x ;
put (x y)(=) ;
run
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
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.