Limit by ERRORS= , special intention.

[Phil_NZ]

Hi SAS Users,

I face a common problem that I saw the notice below in my data when trying to convert from character to numeric type:

NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134495 Year=1992 s2=NA s22=. _ERROR_=1 _N_=133
NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134495 Year=1993 s2=NA s22=. _ERROR_=1 _N_=134
NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134625 Year=1988 s2=NA s22=. _ERROR_=1 _N_=161
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Type=134625 Year=1989 s2=NA s22=. _ERROR_=1 _N_=162

NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to 
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      3309 at 49:5 

My code is:

DATA sheet2_outx;
set sheet2_out;
s22=input(s2,32.);
run;

My data sheet2_out simply is:

Type    Year    s2
131566	1988	$$ER: E100,NO WORLDSCOPE DATA FOR THIS CODE
131566	1989	
131566	1990	
131566	1991	
131566	1992	
131566	1993	
131566	1994	
131566	1995	
131566	1996	
131566	1997	
131566	1998	
131566	1999	
131566	2000	
131566	2001	
131566	2002	
131566	2003	
131566	2004	
131566	2005	
131566	2006	
131566	2007	
131566	2008	
131566	2009	
131566	2010	
131566	2011	
131566	2012	
131566	2013	
131566	2014	
131566	2015	
131566	2016	
131566	2017	
131566	2018	
131566	2019	
131879	1988	NA
131879	1989	NA
131879	1990	NA
131879	1991	NA
131879	1992	NA
131879	1993	NA
131879	1994	NA
131879	1995	NA
131879	1996	NA
131879	1997	3642572
131879	1998	3843093

And the output sheet2_outx is that:

Type    Year    s2      s22                                                                                                                                                            
131566	1988	$$ER: E100,NO WORLDSCOPE DATA FOR THIS CODE	.
131566	1989		    .
131566	1990		    .
131566	1991		    .
131566	1992		    .
131566	1993		    .
131566	1994		    .
131566	1995		    .
131566	1996		    .
131566	1997		    .
131566	1998		    .
131566	1999		    .
131566	2000		    .
131566	2001		    .
131566	2002		    .
131566	2003		    .
131566	2004		    .
131566	2005		    .
131566	2006		    .
131566	2007		    .
131566	2008		    .
131566	2009		    .
131566	2010		    .
131566	2011		    .
131566	2012		    .
131566	2013		    .
131566	2014		    .
131566	2015		    .
131566	2016		    .
131566	2017		    .
131566	2018		    .
131566	2019		    .
131879	1988	NA	    .
131879	1989	NA	    .
131879	1990	NA	    .
131879	1991	NA	    .
131879	1992	NA	    .
131879	1993	NA	    .
131879	1994	NA	    .
131879	1995	NA	    .
131879	1996	NA	    .
131879	1997	3642572	3642572
131879	1998	3843093	3843093

What I want is to convert the variable s2 (characteric variable) to s22 (numeric variable), and all hash observation (e.g:$$ER: E100,NO WORLDSCOPE DATA FOR THIS CODE ) and NA, and blanks and other words to missing variable ".".

So, due to such an intention, whether I do not need to care about the ERROR message in this situation?

Many thanks and warmest regards.

P/S: I also attach my sheet2_out dataset in case it facilitate your investigation.

[SASKiwi]

If you are getting the desired result you can suppress the notes in the log by adding a double question mark like so:

DATA sheet2_outx;
set sheet2_out;
s22=input(s2,?? 32.);
run;

[Phil_NZ]

Hi @SASKiwi 

I am not sure the code I ran and the results as well as the WARNING shown are compatible with my intention mentioned above?

I knew about the ??, which is a kind of suppression of the OPTION, but I am reluctant to use it because it may hide some warnings that may affect the whole results then. I am wondering "??" is mainly used by the person who is quite confident about their data.

Many thanks!

[SASKiwi · Accepted solution]

@Phil_NZ  - The way you have coded it appears to meet your intention. Alternatively you could code for specific values like NA so that unexpected values still produce log notes.

DATA sheet2_outx;
set sheet2_out;
if S2 not in: ('NA', '$$') then s22=input(s2, 32.);
run;

[Tom]

Perhaps you only want to suppress the warning for expected values.

DATA sheet2_outx;
  set sheet2_out;
  if s2 not in ('NA' ' ') then  s22=input(s2,32.);
run; 

[Phil_NZ]

Thank you @Tom  and @SASKiwi !

With the data attached below, when importing, I face the common suggestion :

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

Can you have a look at my dataset and suggest to me how to deal with this NOTE.

My code is

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;
      proc import datafile= "&File." 
                  out= sheet&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= sheet&i.;
      by Type;
      run;

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

      DATA sheet&i._outx;
      set sheet&i._out;

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

   %end;
   
%mend;

Many thanks !

[Tom]

That is just a bug in PROC IMPORT, it shouldn't really be issuing that note as it is not an unexpected issue.  SAS datasets only support fixed length character strings.

Perhaps whoever wrote the code at SAS did not realize it would be used in BASE SAS instead of VIYA?  It is not uncommon for large complex code projects for programmers to not fully understand all of the nuances of the system.

[SASKiwi]

Can you set MSGLEVEL = I, rerun and post the log please? As a general rule I won't copy spreadsheets off the internet for security reasons.

[Phil_NZ]

Hi @SASKiwi ,

as mentioned, I rerun my code with adding the MSGLEVEL=I, the code and LOG are as below:

CODE:

options compress=yes reuse=yes;
options MSGLEVEL=I;

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;
      proc import datafile= "&File." 
                  out= sheet&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= sheet&i.;
      by Type;
      run;

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

      DATA sheet&i._outx;
      set sheet&i._out;

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

   %end;
   
%mend;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\Argentina.xlsx
      , StartSheet= 2
      , EndSheet= 4);

LOG:

NOTE: VARCHAR data type is not supported by the V9 engine. Variable Type has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1988 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1989 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1990 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1991 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1992 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1993 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1994 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1995 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1996 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1997 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1998 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 1999 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2000 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2001 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2002 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2003 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2004 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2005 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2006 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2007 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2008 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2009 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2010 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2011 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2012 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2013 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2014 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2015 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2016 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2017 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2018 has been converted to CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 2019 has been converted to CHAR data type.
NOTE: The import data set has 453 observations and 33 variables.
NOTE: Compressing data set WORK.SHEET2 decreased size by 66.67 percent. 
      Compressed is 1 pages; un-compressed would require 3 pages.
NOTE: WORK.SHEET2 data set was successfully created.

[SASKiwi]

@Phil_NZ  - Looks like Excel is interpreting those columns as VARCHAR and SAS can only handle CHAR and NUM. Your data will still import OK and you could just ignore the notes. To gain more control on your import you could consider saving the Excel sheet as a CSV then you will be able to enforce data type rules and bypass Excel anarchy.