Invalid numeric data in macro SAS

[Phil_NZ]

Hi SAS Users,

I face a common problem called " Invalid numeric data" and it still exists even I check my code quite thoroughly

My code is

/* Macro for all sheets in one file (from 1 to 45)*/
options compress=yes reuse=yes;
options mergenoby=error;
/*OPTIONS MPRINT;*/

%macro ImportAndTranspose(
      File=
	  , outf=
      , StartSheet=
      , EndSheet=
   );
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;
	
    proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet1$A:X";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;
	%end;

	  %else %if &i=34 %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

	  data &outf.&i._outx;
	  set &outf.&i._out;
      if s&i. in: ('NA', '$$', '') then s&i. =".";/********/
	  run;

	  %end;
    
   
    %else %if (&i ne 1) and (&i ne 34) %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

      data &outf.&i._outx;
      set &outf.&i._out;

      if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);
	  else s&i.2=".";
	  drop s&i.;
	  rename s&i.2=s&i.;
      run;
	  %end;

	%end;
 /* Merging*/
%let outfm = %substr(&outf,1,4);
  data &outfm.merge1;
     merge
	  %do i =&StartSheet.+1 %to &EndSheet.;
	    &outf.&i._outx(keep= type year s&i.)
	  %end;
	;
	by type year;
  run;


 data &outfm.merge2;
     merge
	  &outf.&StartSheet.
	  &outfm.merge1 
	 ;
	 by type;
 run;

   
   
%mend;

/*Replicate all files in one folder*/

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
  fname = dread(did,i);
  short_fn= cats(substr(fname, 1,3),'_');
/*Name of the file is Argentina_*/
  cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
      strip(fname),',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');

  call execute(cmd);
end;
keep fname;
run;

Surprisingly, at the log for the sheet35, the warning exists

98        +                                                                          data Arg_sheet35_outx;       set 
Arg_sheet35_out;        if s35 not in: ('NA', '$$', '..') then s352=input(s35, 32.);    else s352=".";    drop s35;    rename 
s352=s35;       run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      98:135   98:206   
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      98:182   
NOTE: Invalid numeric data, 'NA' , at line 98 column 132.
NOTE: Invalid numeric data, '$$' , at line 98 column 132.
NOTE: Invalid numeric data, '..' , at line 98 column 132.
Type=131566 Year=1988 s35=224981952135 s352=224981952135 _ERROR_=1 _N_=1
60                                                         The SAS System                            16:01 Tuesday, January 19, 2021

NOTE: Invalid numeric data, 'NA' , at line 98 column 132.
NOTE: Invalid numeric data, '$$' , at line 98 column 132.
NOTE: Invalid numeric data, '..' , at line 98 column 132.
Type=131566 Year=1989 s35=208880107087 s352=208880107087 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, 'NA' , at line 98 column 132.
NOTE: Invalid numeric data, '$$' , at line 98 column 132.
NOTE: Invalid numeric data, '..' , at line 98 column 132.
......
NG: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.

I think this problem should not exist because I write it clearly in my code is

      if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);
	  else s&i.2=".";
	  drop s&i.;
	  rename s&i.2=s&i.;
      run;
	  %end;

Can you please have a look and help me to sort it out?

Many thanks and warmest regards!

[Phil_NZ]

Hi all,

I had a look on the sheets that the "Invalid data numeric" appears, and I saw that in the sheets where there is none of 'NA', '$$', '..' observation, the 
"Invalid numeric data" and the warnings exists while such notes and warnings do not appear in other sheets where there is at least one observation containing 'NA' or '$$' or '..', is it the explanation ?

Cheers

[ballardw]

Welcome to the joys of relying on Proc Import.

It is very likely that the sheet with the "problem" is because Proc Import brought the data in as numeric where you expect character values, the 'NA', '$$', '..' . So assuming you have character values means that the code can have issues because of assumed data types.

So the

 if s35 not in: ('NA', '$$', '..')

is likely the first cause of the numeric to character conversion. When s35 is numeric the values in the IN comparison list are expected to be numeric and generate the invalid data messages. Easily replicated:

264  data example;
265     s=25;
266     sx='25';
267     if s not in: ('NA', '$$', '..') then s2=input(s,f5.);
268     if sx not in: ('NA', '$$', '..') then sx2=input(s,f5.);
269  run;

NOTE: Character values have been converted to numeric
      values at the places given by: (Line):(Column).
      267:7
NOTE: Numeric values have been converted to character
      values at the places given by: (Line):(Column).
      267:50   268:52
NOTE: Invalid numeric data, 'NA' , at line 267 column 4.
NOTE: Invalid numeric data, '$$' , at line 267 column 4.
NOTE: Invalid numeric data, '..' , at line 267 column 4.
s=25 sx=25 s2=. sx2=. _ERROR_=1 _N_=1

See the line numbers with the invalid all refer to where S is numeric.

So your import macro needs to test the types of the variables created and then use the appropriate code in the data step.

This still leaves conversion messages but no invalid data messages:

data example;
   s=25;
   if vtype(s) = 'N' then s2=s;
   else if s not in: ('NA', '$$', '..') then s2=input(s,f5.);
run;

Another possible solution, depending on the actual reason you are testing for NA $$ and .. is perhaps use a custom informat that handles those values nicely and then no comparison would be needed though you still get conversion messages.

proc format;
invalue svar (upcase)
'NA', '$$','..'=.
;
run;

data example2;
   s='NA';
   if vtype(s) = 'N' then s2=s;
   else s2=input(s,svar.);
run;

Note that instead of

 else s352=".";

which creates a character value that if you intend to create a missing numeric you would be better off with

else s352= . ;

[FreelanceReinh · Accepted solution]

Hi @Phil_NZ,

The reasons for the pesky notes in your log are highlighted below by different colors:

98        +                                                                          data Arg_sheet35_outx;       set 
Arg_sheet35_out;        if s35 not in: ('NA', '$$', '..') then s352=input(s35, 32.);    else s352=".";    drop s35;    rename 
s352=s35;       run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      98:135   98:206   
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      98:182   
NOTE: Invalid numeric data, 'NA' , at line 98 column 132.
NOTE: Invalid numeric data, '$$' , at line 98 column 132.
NOTE: Invalid numeric data, '..' , at line 98 column 132.

In this situation s35 is a numeric variable. Hence it can be compared to numeric values only. Therefore, to check the IF condition, SAS needs to convert the character values 'NA', '$$' and '..' to numeric values. The result of these particular conversions is always a numeric missing value (.) because, obviously, there is no other choice. (With strings containing numbers like '123' or '0.5' or numeric missing values the situation would be different and the notes about "Invalid numeric data" would not occur.)

The first argument of the INPUT function must be a "character constant, variable, or expression" (see documentation). So SAS needs to convert the numeric values in s35 to character values before the informat 32. can be applied. Since 32. is a numeric informat, s352 is created as a numeric variable. But in the ELSE statement you assign a character value (".") to it, which requires another automatic character-to-numeric conversion -- unnecessarily because you could have (and should have) specified s352=.; (without quotes). In an earlier DATA step you set s&i.=".", which is a valid assignment for a character variable, but wouldn't a character missing value (" ") be more useful?

Apparently, your DATA step converting s&i. to a numeric variable is actually redundant (except that it creates dataset &outf.&i._outx) if s&i. is already numeric. So you may want to execute it conditionally, i.e., only if s&i. is a character variable:

%local vt;

proc sql noprint;
select type into :vt trimmed
from dictionary.columns
where libname='WORK' & memname="%upcase(&outf.&i._out)" & name="s&i.";
quit;

data &outf.&i._outx;
set &outf.&i._out;
%if &vt=char %then %do;
  ... code for character-to-numeric conversion ...
%end;
run;

But the root cause for all these issues is that the variable type of s&i. depends on the Excel raw data and on PROC IMPORT's (limited) ability to guess the appropriate variable types. As a minimum, I would help PROC IMPORT guessing by adding a suitable GUESSINGROWS statement. In the long run, however, I would eliminate PROC IMPORT from the process (rather a topic for a new thread).

[PaigeMiller]

Please, when you have an error in the LOG, show us the LOG for at least one PROC or one DATA step above the error, all the way down to the end of the current PROC or DATA step. Do not chop out parts of the log for these steps. The brief portion of the log you show isn't really enough for us to know what the error is. Also please use 

options mprint;

before your macro to improve the usefulness of the log.

[Phil_NZ]

hI @ballardw , @FreelanceReinh , and @PaigeMiller 

Thank you for your insightful notices and suggestions!

After grasping your idea, I twist the code quite a bit as below (2 changed lines and one added line noted by comment) (by changing the order of s&i. condition and numeric-type output of s&i.2 if s&i. in: ('NA', '$$', '..') following @ballardw 

options compress=yes reuse=yes;
options mergenoby=error;
OPTIONS MPRINT;

%macro ImportAndTranspose(
File=
, outf=
, StartSheet=
, EndSheet=
);
%local i;

%do i = &StartSheet. %to &EndSheet.;

%if &i=1 %then %do;

proc import datafile= "&File." 
out= &outf.&i.
dbms= xlsx 
replace;
range= "Sheet1$A:X";
getnames= yes;
run;

proc sort data= &outf.&i.;
by Type;
run;
%end;

%else %if &i=34 %then %do;
proc import datafile= "&File." 
out= &outf.&i.
dbms= xlsx 
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;

proc sort data= &outf.&i.;
by Type;
run;

proc transpose data= &outf.&i. 
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=&outf.&i._out;
run;

data &outf.&i._outx;
set &outf.&i._out;
if s&i. in: ('NA', '$$', '') then s&i. = " ";/*changed line*/
run;

%end;


%else %if (&i ne 1) and (&i ne 34) %then %do;
proc import datafile= "&File." 
out= &outf.&i.
dbms= xlsx 
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;

proc sort data= &outf.&i.;
by Type;
run;

proc transpose data= &outf.&i. 
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=&outf.&i._out;
run;

data &outf.&i._outx;
set &outf.&i._out;
if s&i. in: ('NA', '$$', '..') then s&i.2=.;/*changed line*/
else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.); /*added line*/
drop s&i.;
rename s&i.2=s&i.;
run;
%end;
%end;
/* Merging*/
%let outfm = %substr(&outf,1,4);
data &outfm.merge1;
merge
%do i =&StartSheet.+1 %to &EndSheet.;
&outf.&i._outx(keep= type year s&i.) 
%end;
;
by type year;
run;


data &outfm.merge2;
merge
&outf.&StartSheet.
&outfm.merge1 
;
by type;
run;



%mend;

/*Replicate all files in one folder*/

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
fname = dread(did,i);
short_fn= cats(substr(fname, 1,3),'_');
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
call execute(cmd);
end;
keep fname;
run;

To answer @FreelanceReinh , thank you for your comprehensive answer, I tried libgen statement instead of proc import but it does not work in my system because of the error

ERROR: Invalid physical name for library MYXL

I also raise a topic about this error but the answer so far is still ambiguous

https://communities.sas.com/t5/SAS-Programming/Is-there-anyway-to-import-a-file-xlsm-to-xlsx-in-SAS/...

And hi @PaigeMiller , I am sorry about this inconvenience. I also use OPTION MPRINT when running macro, just because when posting this post, I just want it be simplified quite a bit. So, sorry for your experience when reading my log, now I posted the log of the proc beforehand.

This is MPRINT log

PRINT(IMPORTANDTRANSPOSE):   proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet1 dbms= 
xlsx replace;
MPRINT(IMPORTANDTRANSPOSE):   range= "Sheet1$A:X";
MPRINT(IMPORTANDTRANSPOSE):   getnames= yes;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc sort data= Arg_sheet1;
MPRINT(IMPORTANDTRANSPOSE):   by Type;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx" out= Arg_sheet2 dbms= 
xlsx replace;
MPRINT(IMPORTANDTRANSPOSE):   range= "Sheet2$A:AG";
MPRINT(IMPORTANDTRANSPOSE):   getnames= yes;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc sort data= Arg_sheet2;
MPRINT(IMPORTANDTRANSPOSE):   by Type;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc transpose data= Arg_sheet2 out= Arg_sheet2_out(rename=(COL1=s2 _NAME_=Year) drop=_label_);
MPRINT(IMPORTANDTRANSPOSE):   by Type;
MPRINT(IMPORTANDTRANSPOSE):   VAR '1988'N - '2019'N;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc print data=Arg_sheet2_out;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   data Arg_sheet2_outx;
MPRINT(IMPORTANDTRANSPOSE):   set Arg_sheet2_out;
MPRINT(IMPORTANDTRANSPOSE):   if s2 in: ('NA', '$$', '..') then s22=.;
MPRINT(IMPORTANDTRANSPOSE):   else if s2 not in: ('NA', '$$', '..') then s22=input(s2, 32.);
MPRINT(IMPORTANDTRANSPOSE):   drop s2;
MPRINT(IMPORTANDTRANSPOSE):   rename s22=s2;
MPRINT(IMPORTANDTRANSPOSE):   run;
/*.
.
similar from s2 til s45 except s34
.*/
MPRINT(IMPORTANDTRANSPOSE):   proc transpose data= Arg_sheet34 out= Arg_sheet34_out(rename=(COL1=s34 _NAME_=Year) drop=_label_);
MPRINT(IMPORTANDTRANSPOSE):   by Type;
MPRINT(IMPORTANDTRANSPOSE):   VAR '1988'N - '2019'N;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   proc print data=Arg_sheet34_out;
MPRINT(IMPORTANDTRANSPOSE):   run;
MPRINT(IMPORTANDTRANSPOSE):   data Arg_sheet34_outx;
MPRINT(IMPORTANDTRANSPOSE):   set Arg_sheet34_out;
MPRINT(IMPORTANDTRANSPOSE):   if s34 in: ('NA', '$$', '') then s34 = " ";
MPRINT(IMPORTANDTRANSPOSE):   run;

And the log is as below

 + proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx"                    out= Arg_sheet1          
         dbms= xlsx                    replace;
1         +
           range= "Sheet1$A:X";           getnames= yes;       run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
      options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 24 variables.
NOTE: Compressing data set WORK.ARG_SHEET1 decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: WORK.ARG_SHEET1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.11 seconds
      cpu time            0.11 seconds
      

1         +
              proc
2         + sort data= Arg_sheet1;       by Type;       run;

NOTE: There were 453 observations read from the data set WORK.ARG_SHEET1.
NOTE: The data set WORK.ARG_SHEET1 has 453 observations and 24 variables.
21                                                         The SAS System                          10:21 Wednesday, January 20, 2021

NOTE: Compressing data set WORK.ARG_SHEET1 decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

2         +                                                        proc import datafile= "C:\Users\pnguyen\Desktop\New 
folder\Argentina_.xlsx"                    out= Arg_sheet2                   dbms= xlsx                    replace;
2         +
           range= "Sheet2$A:AG";
3         +           getnames= yes;       run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
      options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET2 decreased size by 66.67 percent. 
      Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: WORK.ARG_SHEET2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.07 seconds
      cpu time            0.04 seconds
      

3         +                                                  proc sort data= Arg_sheet2;       by Type;       run;

NOTE: There were 453 observations read from the data set WORK.ARG_SHEET2.
NOTE: The data set WORK.ARG_SHEET2 has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET2 decreased size by 66.67 percent. 
      Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

3         +                                                                                                               proc 
transpose data= Arg_sheet2              out= Arg_sheet2_out(rename=(COL1=s2 _NAME_=Year) drop=_label_);          by Type;          
VAR
4         + '1988'N - '2019'N;       run;

NOTE: There were 453 observations read from the data set WORK.ARG_SHEET2.
NOTE: The data set WORK.ARG_SHEET2_OUT has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET2_OUT decreased size by 46.15 percent. 
      Compressed is 7 pages; un-compressed would require 13 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

4         +                                  proc print data=Arg_sheet2_out;    run;

NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET2_OUT.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           6.52 seconds
      cpu time            6.09 seconds
      

22                                                         The SAS System                          10:21 Wednesday, January 20, 2021

4         +                                                                                 data Arg_sheet2_outx;       set 
Arg_sheet2_out;    if s2 in: ('NA', '$$', '..') then s22=.;    else if s2 not in: ('NA', '$$', '..') then s22=input(s2, 32.);    
drop s2;
5         + rename s22=s2;       run;

NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET2_OUT.
NOTE: The data set WORK.ARG_SHEET2_OUTX has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET2_OUTX increased size by 16.67 percent. 
      Compressed is 7 pages; un-compressed would require 6 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
/* Eveythingh run smoothly til s35*/
proc import datafile= "C:\Users\pnguyen\Desktop\New folder\Argentina_.xlsx"                    out= Arg_sheet35
101       + dbms= xlsx                    replace;
101       +                                                  range= "Sheet35$A:AG";           getnames= yes;       run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
      options MSGLEVEL=I.
NOTE: The import data set has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET35 decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: WORK.ARG_SHEET35 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.17 seconds
      

101       +
proc sort data= Arg_sheet35;       by Type;       run;

NOTE: There were 453 observations read from the data set WORK.ARG_SHEET35.
NOTE: The data set WORK.ARG_SHEET35 has 453 observations and 33 variables.
NOTE: Compressing data set WORK.ARG_SHEET35 decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

101       +
proc transpose data= Arg_sheet35              out= Arg_sheet35_out
59                                                         The SAS System                          10:21 Wednesday, January 20, 2021

102       +(rename=(COL1=s35 _NAME_=Year) drop=_label_);          by Type;          VAR '1988'N - '2019'N;       run;

NOTE: There were 453 observations read from the data set WORK.ARG_SHEET35.
NOTE: The data set WORK.ARG_SHEET35_OUT has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET35_OUT increased size by 33.33 percent. 
      Compressed is 8 pages; un-compressed would require 6 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      

102       +                                                                                                              proc print 
data=Arg_sheet35_out;    run;

NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET35_OUT.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           6.54 seconds
      cpu time            6.32 seconds
      

102       +
data Arg_sheet35_outx;       set Arg_sheet35_out;    if s35 in: ('NA', '$$', '..') then s352=.;
103       +  else if s35 not in: ('NA', '$$', '..') then s352=input(s35, 32.);    drop s35;    rename s352=s35;       run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      102:215   103:11    
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      103:58   
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1988 s35=224981952135 s352=224981952135 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1989 s35=208880107087 s352=208880107087 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1990 s35=203726588309 s352=203726588309 _ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1991 s35=222333162872 s352=222333162872 _ERROR_=1 _N_=4
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
60                                                         The SAS System                          10:21 Wednesday, January 20, 2021

NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1992 s35=239980394235 s352=239980394235 _ERROR_=1 _N_=5
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1993 s35=259675534968 s352=259675534968 _ERROR_=1 _N_=6
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1994 s35=274830720367 s352=274830720367 _ERROR_=1 _N_=7
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1995 s35=267011210298 s352=267011210298 _ERROR_=1 _N_=8
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1996 s35=281768091695 s352=281768091695 _ERROR_=1 _N_=9
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1997 s35=304622433397 s352=304622433397 _ERROR_=1 _N_=10
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1998 s35=316350941905 s352=316350941905 _ERROR_=1 _N_=11
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=1999 s35=305641016669 s352=305641016669 _ERROR_=1 _N_=12
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
61                                                         The SAS System                          10:21 Wednesday, January 20, 2021

NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2000 s35=303229512290 s352=303229512290 _ERROR_=1 _N_=13
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2001 s35=289860609223 s352=289860609223 _ERROR_=1 _N_=14
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2002 s35=258281789127 s352=258281789127 _ERROR_=1 _N_=15
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
NOTE: Invalid numeric data, '..' , at line 103 column 8.
Type=131566 Year=2003 s35=281106256201 s352=281106256201 _ERROR_=1 _N_=16
NOTE: Invalid numeric data, 'NA' , at line 102 column 212.
NOTE: Invalid numeric data, '$$' , at line 102 column 212.
NOTE: Invalid numeric data, '..' , at line 102 column 212.
NOTE: Invalid numeric data, 'NA' , at line 103 column 8.
NOTE: Over 100 NOTES, additional NOTES suppressed.
NOTE: Invalid numeric data, '$$' , at line 103 column 8.
Type=131566 Year=2004 s35=306488951657 s352=306488951657 _ERROR_=1 _N_=17
Type=131566 Year=2005 s35=333618311350 s352=333618311350 _ERROR_=1 _N_=18
Type=131566 Year=2006 s35=360465082298 s352=360465082298 _ERROR_=1 _N_=19
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Type=131566 Year=2007 s35=392934518438 s352=392934518438 _ERROR_=1 _N_=20
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET35_OUT.
NOTE: The data set WORK.ARG_SHEET35_OUTX has 14496 observations and 3 variables.
NOTE: Compressing data set WORK.ARG_SHEET35_OUTX increased size by 33.33 percent. 
      Compressed is 8 pages; un-compressed would require 6 pages.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

I also added the data of the file Argentina_ that I used in my regression, can you please help me to have a look?

[SASKiwi]

Try this more flexible approach for checking for character or numeric data:

if input(s&i., ?? 32.) = . then s&i.2=.;
else s&i.2=input(s&i., 32.);

[Phil_NZ]

Hi @SASKiwi 

Thank you very much, I never thought that the solution is simple like that.

1. I just want to reaffirm whether the code you just post

if input(s&i., ?? 32.) = . then s&i.2=.;
else s&i.2=input(s&i., 32.);

can replace my old code below ?

if s&i. in: ('NA', '$$', '..') then s&i.2=.;
	  else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);

2. And can I ask whether the meaning of the code

input(s&i., ?? 32.) = .;

 works in case the import is character value?

Many thanks and best regards,

[SASKiwi]

@Phil_NZ  - Your code replacement assumption is correct.

The addition of the two question marks suppresses SAS log warnings as we are only testing if the variable values in question will convert correctly to numbers.