Variable test in not an object when using vartype function

[Phil_NZ]

Hi all SAS Users,

Today when I try a condition, the error bar chat shows "Variable test is not an object". Could you please lemme know how to correct it?

libname test "C:\Users\pnguyen\Desktop\New folder";data haa_;
	set test.argentina_filterf;
		if (vartype(test.argentina_filterf,BDATE)="N") then do;
			SASDATE = input(BDATE,32.)+'30DEC1899'd ;
			format SASDATE yymmdd10.;
    		fir_age = log(1+(2020-year(SASDATE)));
		end;
		else 
			fir_age = log(1+(2020-year(BDATE)));
run;	

The log

28         data haa_;
29         	set test.argentina_filterf;
30         		if (vartype(test.argentina_filterf,BDATE)="N") then do;
                         ______________________
                         557
ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
ERROR 557-185: Variable test is not an object.

NOTE: The SAS System stopped processing this step because of errors.

I also follow a link and see my condition "if" is appropriate from my understanding of the document.

Many thanks and warm regards.

P/S: I also attached two files here while BDATE of  "argentina_filterf" is in character type and that of "zambia_filterf" is in numeric type for testing purpose.

[Tom]

The reason for the error is you have used a string with a period in it for the variable name you are passing to the VARTYPE() function.  In SAS a name with a period is used to reference an object, like a hash object, hence the error message.

What variable are you trying to test? BDATE?  

set test.argentina_filterf;
if vtype(BDATE)="N" then do;

If you want to use VARTYPE() then you need to have first opened the dataset with the OPEN() function like in the photograph you posted. 

[Phil_NZ]

Hi @Tom 

Thank you, It works, just wondering if the code below is okay to you( yes the one I want to test is BDATE)?

data haa_;
	set test.argentina_filterf;
		if (vtype(BDATE)="C") then do;
			SASDATE = input(BDATE,32.)+'30DEC1899'd ;
			format SASDATE yymmdd10.;
		end;
		else SASDATE=BDATE;

	fir_age = log(1+(2020-year(SASDATE)));
	
run;	

Warm regards.

[Tom · Accepted solution]

It will work, but it does not prevent the notes about type conversion, which ever type BDATE has, since the code of the data step has places where BDATE is interpreted as both numeric and character.

224   data test;
225     bdate='42000';
226     if vtype(bdate)='C' then sasdate=input(bdate,32.) + '30DEC1899'd ;
227     else sasdate=bdate ;
228     format sasdate date9.;
229     put (_all_) (=);
230   run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      227:16
bdate=42000 sasdate=27DEC2014
NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


231
232   data test;
233     bdate='27DEC2014'd;
234     if vtype(bdate)='C' then sasdate=input(bdate,32.) + '30DEC1899'd ;
235     else sasdate=bdate ;
236     format sasdate date9.;
237     put (_all_) (=);
238   run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      234:42
bdate=20084 sasdate=27DEC2014
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

[Phil_NZ]

Hi @Tom 

You are totally right! Is this an error? And if it is an error, is there any way to fix it then ?

Warmest regards.

[Tom]

It is not an "ERROR" since it is expected behavior, but many times that type of message does mean there is an error.

At the minimum you should put a comment in the code that the message is expected.

To prevent it you need to use code generation.  For example via MACRO logic.  So first detect the type and then generate a data step that only include the statement that is appropriate for that type.

239   data test;
240     bdate='42000';
241   run;

NOTE: The data set WORK.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


242
243   data _null_;
244     if 0 then set test;
245     call symputx('bdate_type',vtype(bdate));
246     stop;
247   run;

NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


248
249   data want ;
250     set test;
251   %if &bdate_type=C %then %do;
252     sasdate=input(bdate,32.) + '30DEC1899'd ;
253   %end;
254   %else %do;
255     sasdate=bdate ;
256   %end;
257     format sasdate date9.;
258     put (_all_) (=);
259   run;

bdate=42000 sasdate=27DEC2014
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.WANT has 1 observations and 2 variables.

[Phil_NZ]

Hi @Tom 

Many thanks for a very dedicated explanation.

I have two questions here.

1. So, you mean, in my case, the result is not wrong if I still apply my current code, the log just generate note and warning "Limit set by ERRORS= option reached." 

libname test "C:\Users\pnguyen\Desktop\merge2";
data haa_;
set test.argentina_merge2;
		if (vtype(BDATE)="C") then do;
			SASDATE = input(BDATE,32.)+'30DEC1899'd ;
			format SASDATE yymmdd10.;
		end;
		else SASDATE=BDATE;

	fir_age = log(1+(2020-year(SASDATE)));
	
run;

2. Could you please insert your macro into my above code or hint me on how to adapt my code above including your macro?

Many thanks and warmest regards.

[Tom]

This is how you would combine it with your code.

libname test "C:\Users\pnguyen\Desktop\merge2";
data _null_;
  if 0 then set test.argentina_merge2;
  call symputx('bdate_type',vtype(bdate));
  stop;
run;

data haa_;
  set test.argentina_merge2;
%if &bdate_type=C %then %do;
  SASDATE = input(BDATE,32.)+'30DEC1899'd ;
%end;
%else %do;
  else SASDATE=BDATE;
%end;
  format SASDATE yymmdd10.;
  if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;

But If you are getting ERROR messages then something else is going on.

Perhaps missing values of SASDATE in the LOG() function call?   I added a IF/THEN test to skip the LOG() function if SASDATE is missing.

Perhaps character values of BDATE that are not valid integers in the INPUT() function call are generating errors?  

One reason why BDATE in an EXCEL file might end up as a character string in SAS dataset is because someone entered text version of a date into the EXCEL file.  So you need to look at the value of BDATE in the error messages to see what is happening.  You might want to make the SAS code that you generate when BDATE is character to be smarter.

For example you might only use the existing technique when the value is 5 characters long (which should cover most Excel dates as digit strings) and instead try the ANYDTDTE. informat in case the field actually has strings like '2020-01-01' or other things that look like dates.

%if &bdate_type=C %then %do;
  if length(bdate)=5 then SASDATE = input(BDATE,32.)+'30DEC1899'd ;
  else SASDATE= input(BDATE,anydtdte.);
%end;

[Tom]

From your other messages perhaps the ERRORs in your log are caused by NA values in the BDATE variable.  (Did you perhaps get this data file from an R programmer?)  If so then just test for the NA strings so they don't get pushed into the INPUT() function call.

%if &bdate_type=C %then %do;
  if upcase(bdate) ne 'NA' then SASDATE = input(BDATE,32.)+'30DEC1899'd ;
%end;

[Phil_NZ]

Hi @Tom 

Amazing support and comprehensive answer

I tested the code successfully outside the macro.

I just want to conclude a little bit:

I did not get the data from R programmer, it is from Excel (I just check the original files).

I agree with all your recent two posts, now the code in total is

libname test "C:\Users\pnguyen\Desktop\merge2";
data _null_;
 	if 0 then set test.argentina_merge2;
  call symputx('bdate_type',vtype(bdate));
  stop;
run;

data haa_;
  set test.argentina_merge2;
%if &bdate_type=C %then %do;
  if length(bdate)=5 and upcase(bdate) ne 'NA' then 
	SASDATE = input(BDATE,32.)+'30DEC1899'd ;
  else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
  else SASDATE=BDATE;
%end;
  format SASDATE yymmdd10.;
  if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;	

Can I ask a further question?

That when I put it into a macro, it turns out an errors regarding the code "if not missing"

 The code is

data _null_;
 	if 0 then set &outfm.merge2;
  call symputx('bdate_type',vtype(bdate));
  stop;
run;

data &outfm.merge2_(drop=NAME);
  set &outfm.merge2;
%if &bdate_type=C %then %do;
  if (length(bdate)=5) and (upcase(bdate) ne 'NA') then 
	SASDATE = input(BDATE,32.)+'30DEC1899'd ;
  else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
  else SASDATE=BDATE;
%end;
  format SASDATE yymmdd10.;
  if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;

The error log is

134       +                                                                                                                         
data ARGENTINA_merge2_(drop=NAME);   set ARGENTINA_merge2;   else SASDATE=BDATE;   format SASDATE yymmdd10.;   if not

____                                                                                                                                
_________                                                                                                                           

160
484
134      !+missing(sasdate)
ERROR 160-185: No matching IF-THEN clause.

NOTE 484-185: Format $YYMMDD was not found or could not be loaded.

135       + then fir_age = log(1+(2020-year(SASDATE))); run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      135:34   
88                                                         The SAS System                          23:21 Saturday, February 13, 2021

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ARGENTINA_MERGE2_ may be incomplete.  When this step was stopped there were 0 observations and 70 
         variables.
WARNING: Data set WORK.ARGENTINA_MERGE2_ was not replaced because this step was stopped.

My whole code is

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

%macro ImportAndTranspose(
      File=
	  , cur=
	  , 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;

	  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;

      data &outf.&i._outx;
      set &outf.&i._out;
	  if input(s&i., ?? 32.) = . then s&i.2=.;/*added line 21_Jan*/
      else s&i.2=input(s&i., 32.);/*added line 21_Jan*/
	  /*if s&i. in: ('NA', '$$', '..') then s&i.2=.;
	  else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);*/
	  drop s&i.;
	  rename s&i.2=s&i.;
      run;
	  %end;
	%end;
 /* Merging*/
%let outfm = %substr(&outf,1,%length(&outf)-5);
  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;

data _null_;
 	if 0 then set &outfm.merge2;
  call symputx('bdate_type',vtype(bdate));
  stop;
run;

data &outfm.merge2_(drop=NAME);
  set &outfm.merge2;
%if &bdate_type=C %then %do;
  if (length(bdate)=5) and (upcase(bdate) ne 'NA') then 
	SASDATE = input(BDATE,32.)+'30DEC1899'd ;
  else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
  else SASDATE=BDATE;
%end;
  format SASDATE yymmdd10.;
  if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;

*********************************************;
 /* Delete file results to reduce operating*/
 *************;
%local i;
   
   %do i = &StartSheet.+1 %to &EndSheet.;
   *+1 because sheet 1 behave differently*;
      proc delete data=&outf.&i.;
      proc delete data=&outf.&i._out;
	  proc delete data=&outf.&i._outx;
	%end;
 *****************;
   proc delete data=&outf.1;
   proc delete data=&outfm.merge1;

 
**********************************************;

************************************************;
**EXPORT DATASET**;
/*https://communities.sas.com/t5/SAS-Programming/making-a-sas7bdat-file/td-p/417487*/ 
libname myfolder 'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Cfiltering';
proc copy in=work out=myfolder;
select &outfm.merge2_;
run;
quit;

/*proc delete data=&outfm.filter;*/

%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 = scan(dread(did,i),1,'.');
  length short_fn $29 currency $3 ;
  short_fn= cats(substr(fname, 1,length(fname)-3),'_');
  currency=substr(fname,length(fname)-2);

  cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
      strip(fname),
      ',cur=',currency,
      ',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');

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

Warmest regards.

[ballardw]

Please repeat this to yourself as many times as it takes to stick:

Excel columns do not have a data type. Any cell can hold anything. So data "imported" from Excel is subject to all sorts of garbage.

I read dozens of Excel files weekly. The only ones that I do not turn into CSV so I can control the content, like variable type and lengths is one that is generated by a DBMS program and exported. At that I have a SAS PCFILES library to use the data by selecting values from columns into known variable names of specified types because the original file has multiple header lines to ignore. The source for this is known as to fixed rows of header information and column order with a data sharing document so I know which columns will hold what sort of content.

One of the data sources I have to examine every single CSV generated from the Excel because the column "names" change, the row the data starts on changes, the order of the columns changes and the number of columns changes frequently.

Reliance on Proc Import is job security in that someone gets to "fix" imported data constantly.

[Phil_NZ]

And can I ask, whether you have one redundant else here?

%else %do;
  else SASDATE=BDATE;
%end;

Warm regards.