Behavior of Conditional Statement with formats

[alawton]

I have Excel workbooks delivered to me that I must combine and manipulate. I wanted to account for instances where the sender formats dates (specifically variable F11) in either text or number formats. To this end, I am using a macro that produces a T/F response when evaluating the appropriate variable. I have written a conditional statement that I hoped to say: if it is a number then use put statement -- if it is text, copy it over in its current form. To test the program, I used a field I knew to be text. When I run the program, an error appears in the log but the program runs seemingly correctly. The error reads:

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

If I test the program with a number variable, it runs without errors. Why am I receiving an error message related to a condition that is untrue? It is my expectation that SAS would not process the apodosis of condition when the protasis is untrue. Thanks for the explanation in advance.

%macro isnum(str);
verify(trim(left(&str)),'0123456789')=0 or /*number only*/
verify(trim(left(&str)),'0123456789.')=0
and not indexc(substr(&str,indexc(&str,'.')+1), '.') or /*allow only one '.'*/
verify(trim(left(&str)),'0123456789.+-')=0
and not indexc(substr(&str,indexc(&str,'.')+1), '.')
and (indexc(&str,'+-')=1
and not indexc(substr(&str,2),'+-') /*allow only one leading '+' or '-'*/
and indexc(&str,'0123456789.') > 1 ) or /* '+-' must followed by number*/
compress(&str)='' /*'', ' ', or multiple ' ' is numeric*/
%mend;

data nrcinput;
set nrc.'Sheet1$'n;
if F1 = '' or F1 = 'FName' then delete;

if %isnum(F11) then
  dob=put(F11,mmddyy10.);
else dob=F11;
run;

[data_null__]

The message is because F11 is a SAS Character Variable.

[alawton]

Yes, I understand the problem of numeric formats and character variables. However, I was attempting to dodge this issue by separating numeric handling and character handling in a conditional statement.

What I don't understand is why the untrue part of the statement is evaluated at all. I thought if SAS evaluated a conditional statement to be false, it would cease processing that step and procede to the next line (in this case, the else statement).

[LinusH]

I'm not sure what kind of situations your are preparing for.

If F11 is a character variable, but you are trying to find out if the current observations value for F11 is numerical?

Since F11 in  this case a character defined variable, you cannot put it using a numerical format.

What do you expect to come out of that put function? Do you store date values in character variable, makes no sense...? Please provide some sample input data to that step...

[alawton]

I am receiving Excel files from a number of people that I will have to combine and output to a delimited file (I'm the middle-man). I have dictated to senders that all fields must be character and not Excel dates. All columns in the Excel sheets will have the same number of columns and in the same order. The files should be easily combinable, provided that senders followed my instructions.

In my initial run of the data, the some senders correctly sent dates as text and some dates sent Excel dates. I reminded everyone to send the Excel files formatted as text, but I wanted to write something that would anticipate this error if/when it occurs again.

My desired outcome is a character variable containing a character representation of the date.

[data_null__]

If PROC IMPORT detects a EXCEL date field then F11 will be numeric right?  If F11 is SAS numeric you will need to know that ahead of time.

One easy way to convert all the variables to character is to do a "Flip-Flop" transpose.

proc transpose ...

   by row-id-variable;

   var F:;

   run;

proc transpose ....;

   by row-id-variable;

   var col1;

   id _name_;

   run;

[Quentin]

Hi alawton,

New big words for me: apodosis and protasis!

I think the point you are missing is that SAS first compiles the data step, and then it executes it.

All of the data step code is compiled.  The conditional IF statement, including the apodosis and protasis, is compiled.  The conditioanal IF statement controls whether or not the apodosis is executed.

The error message you get is a compile-time error.

Below is a simple example.  Because DATE is a character variable (SAS knows this at compile time), SAS knows that MMDDYY cannot be used with a character variable.  It then assumes you forgot to add a $ sign.  So it adds a $ sign for you (some might not like this behavior), then errors when it realizes there is no format named $mmddyy10.

When DATE is a numeric variable (second step below), the code "works" because SAS does an implicit numeric to character conversion for you  (again, some don't like that behavior).

If you do not know whether DATE will be character or numeric, then I think your options are to force it be one in advance (as  _null_ suggests), or figure it out from the source data /metadata.

You cannot uses a DATA STEP IF to control which code is compiled.  But you can use a macro %IF to control which code is compiled.  So could end up with something like:

%if %VarType(data=mydata,var=date)=N %then %do;

  dob=put(date,mmddyy10.);

%end;

%else %do;

  dob=date;

%end;

Note that %VARTYPE would be a macro function, which returns N if a variable is numeric.

49   data crash;
50     date="house";
51     if verify(date,'0123456789')=0 then do;
52       dob=put(date,mmddyy10.);
                      ---------
                      48
ERROR 48-59: The format $MMDDYY was not found or could not be loaded.

53     end;
54     else dob=date;
55     put date= dob=;
56   run;

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

57
58   data works;
59     date="14Jun2013"d;
60     if verify(date,'0123456789')=0 then do;
61       dob=put(date,mmddyy10.);
62     end;
63     else dob=date;
64     put date= dob=;
65   run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      60:13   63:12
date=19523 dob=19523
NOTE: The data set WORK.WORKS has 1 observations and 2 variables.

HTH,

--Q.

[alawton]

Thank you Quentin! Your response makes sense; I thought compilation could be the culprit but I could not come up with the means.

However, I am struggling with the application of the macro.  I am presented with the error:

ERROR: Required operator not found in expression: vtype(&str) = 'N'

ERROR: The macro NUMFIX will stop executing.

What am I missing?

%macro numfix(str);
%if vtype(&str) = 'N' %then %do;
  dob=put(&str,mmddyy10.);
%end;
%else %do;
  dob=&str;
%end;
%mend;

data nrcinput;
set nrc.'Sheet1$'n;

if F1 = '' or F1 = 'FName' then delete;

%numfix(F11)
run;

[Tom]

You need to find the type BEFORE you start the new data step.

You could do this with VTYPE function with a DATA _NULL_;

data _null_;

  if 0 then set nrc.'Sheet1$'n;

  call symputx('VTYPE',vtype(F11));

run;

data ....

%if "&vtype" = "N" %then ....

%else ...

[Quentin]

Hi,

To implement my suggestion, you would need to write a macro function which would figure out whether a variable was numeric or character.

something like:

%macro VarType(data=,var=);
  %local dsid varnum vartype rc;
  %let dsid    = %sysfunc( open   ( &data)           );
  %let varnum  = %sysfunc( varnum ( &dsid, &var    ) );
  %let vartype = %sysfunc( vartype(&dsid, &varnum)   );
  %let rc      = %sysfunc( close  ( &dsid )          );
&vartype
%mend VarType;

*Test the macro;
%put Name is : %vartype(data=sashelp.class,var=name);
%put Age  is : %vartype(data=sashelp.class,var=age);

Which you could then use in your setting, something like:

data nrcinput;
  set nrc.'Sheet1$'n;
  if F1 = '' or F1 = 'FName' then delete;

  %if %vartype(data=nrc.'Sheet1$'n,var=F1)=N %then %do;
    dob=put(&str,mmddyy10.);
  %end;
  %else %do;
    dob=&str;
  %end;

run;

Above is untested.  I haven't checked to see if %vartype(data=nrc.'Sheet1$'n,var=F1) will work when the data= specifies an Excel sheet.

[Tom]

Why not use VTYPE function?

if vtype(F11)='N'  then dob=put(F11,mmddyy10.);

else dob=F11 ;

[ChrisNZ]

The code above will still gives you the "NOTE: Numeric values have been converted to character values" message.

Your suggestion @ 14-Jun-2013 15:53 is better imho.

[Tom]

Not that I recommend it as the right solution, but you could use SYMPUT and SYMGET on every observation to convert F11 to character.

call symputx('dummy_f11',f11);

dob = symget('dummy_f11');

if vtype(f11) = 'N' then dob = put(input(dob,10.),yymmdd.) ;

[ChrisNZ · Accepted solution]

Or use the cat function do do the same convertion:

DOB=cats(F11);

if vtype(F11) = 'N' then DOB = put(input(DOB,32.),yymmdd10.) ;