BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alawton
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Or use the cat function do do the same convertion:

DOB=cats(F11);

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

View solution in original post

13 REPLIES 13
data_null__
Jade | Level 19

The message is because F11 is a SAS Character Variable.

alawton
Calcite | Level 5

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
Tourmaline | Level 20

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...

Data never sleeps
alawton
Calcite | Level 5

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__
Jade | Level 19

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
alawton
Calcite | Level 5

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
Super User Tom
Super User

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

Why not use VTYPE function?

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

else dob=F11 ;

ChrisNZ
Tourmaline | Level 20

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

Your

Tom
Super User Tom
Super User

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
Tourmaline | Level 20

Or use the cat function do do the same convertion:

DOB=cats(F11);

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2359 views
  • 7 likes
  • 6 in conversation