Using a date in %DO loop with SYSFUNC - What is wrong?

[stuart_snap]

Hi all,

Hopefully this is an easy one for someone, but I am beating my head.  It's probably something simple.

I have a simple macro to apply formatting to a date var based on the day of the month, if I hard code a date into the DATE function, the macro works fine.  But, if I use the date variable that I need to use, it errors out.   Additionally, I am unable to use IN in the %IF statement... I can only use = with a series of ORs.  Does anyone see what the problem is?  The date field, offer_expiration is in DATE9. format.  The error message is below the code.

Thanks!!!

%macro form();

data catawba;

set final_wb;

%if %sysfunc(day(offer_expiration)) = 1 or %sysfunc(day(offer_expiration)) = 21 or %sysfunc(day('21JUN2014'D)) = 31 %then %do;

format exp_dateN MMDDYY10. exp_dateA2 myfmtst14.;

exp_dateN=offer_expiration;

exp_dateA2=offer_expiration;

exp_dateA=strip(trim(put(exp_dateA2,myfmtst14.)));

keep card_code Dealer_Name New_Offer Contact_Person Address_1 Address_2 City state zipcode exp_dateN exp_dateA;

%end;

%else

%if %sysfunc(day(offer_expiration)) = 2 or %sysfunc(day(offer_expiration)) = 22 %then %do;

format exp_dateN MMDDYY10. exp_dateA2 myfmtnd14.;

exp_dateN=offer_expiration;

exp_dateA2=offer_expiration;

exp_dateA=strip(trim(put(exp_dateA2,myfmtnd14.)));

keep card_code Dealer_Name New_Offer Contact_Person Address_1 Address_2 City state zipcode exp_dateN exp_dateA;

%end;

%else

%if %sysfunc(day(offer_expiration)) = 3 or %sysfunc(day(offer_expiration)) = 23 %then %do;

format exp_dateN MMDDYY10. exp_dateA2 myfmtrd14.;

exp_dateN=offer_expiration;

exp_dateA2=offer_expiration;

exp_dateA=strip(trim(put(exp_dateA2,myfmtrd14.)));

keep card_code Dealer_Name New_Offer Contact_Person Address_1 Address_2 City state zipcode exp_dateN exp_dateA;

%end;

%else %do; format exp_dateN MMDDYY10. exp_dateA2 myfmtth14.;

exp_dateN=offer_expiration;

exp_dateA2=offer_expiration;

exp_dateA=strip(trim(put(exp_dateA2,myfmtth14.)));

keep card_code Dealer_Name New_Offer Contact_Person Address_1 Address_2 City state zipcode exp_dateN exp_dateA;

%end;

run;

%mend;

%form();

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: Argument 1 to function DAY referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

[Quentin]

Hi,

It looks like you have a few different issues going on.

1.  The cause of the error messages is you are using a macro %IF statement and trying to test the value of a data step variable, Offer_Expiration.  That won't work.  The macro language does not know about data step variable.  Perhaps you want a data step IF rather than macro %IF.

2.  It's hard to see what your logic is since the code inside each %do loop is the same.  Note if you do change to data step IF statements, you cannot make the FORMAT or KEEP statements conditional on an IF statement, as they are data step compile time statements (i.e. impact the creation of the PDV when the data step is compiled), rather than data step execution time statements.

3.  I suppose another possiblity is that you meant for Offer_Expiration to be a macro variable &Offer_Expiration that would resolve to a SAS Date value.

HTH,

--Q.

[ballardw]

Are you expecting the value of the variable offer_expiration in the data set final_wb to be used?

The macro %if statement is not part of the dataset code, it is executed "outside" of the data step to generate code.

You may be misunderstanding how SAS formats work. A variable within a dataset can only have one format. You might be able to accomplish what you are attempting with a specifici format OR you need to create new variables with the formatted values neither of which would require a macro %if.

If you provide the code for the formats myfmtst14, myfmtnd14, myfmtrd14 etc. there may be other options.

[stuart_snap]

Thanks all.

Let me ask the question another way to see if that helps.  I am creating a list for a mail vendor.  They need a text field with the format, for example, "June 30th".  That date represents the offer_expiration field in my dataset.  I have created 4 custom formats that add either the TH, ST, RD, or ND to the end of the number.  The above macro was attempting to add the correct suffix, depending on the expiration.  If we ignore the original macro, do you have any suggestions for getting the format applied based on the expiration date?  A sample of the existing code for the formats are below.

Thoughts?  Any help is greatly appreciated!

proc format ;

picture myfmtth low-high = '%B %dth'

(datatype = date) ;

run ;

proc format ;

picture myfmtst low-high = '%B %dst' (datatype = date) ;

run ;

[Quentin]

Here is a brute force approach with just two categories.   I'm sure you'll get better solutions.  Feels like there should be a format that would make this easier....

proc format ;
 picture myfmtst (default=20) 
    low-high = '%B %dst' 
    (datatype = date) 
    
  ;
 picture myfmtth (default=20) 
    low-high = '%B %dth'
    (datatype = date)
 ;
run ;

data have;
  do offer_expiration="01Jan2014"d to "31Jan2014"d;
    output;
  end;
  format offer_expiration mmddyy10.;
run;

data want;
  set have;
  if day(offer_expiration) in (1,21,31) then exp_dateA=put(offer_expiration,myfmtst.);
  else exp_dateA=put(offer_expiration,myfmtth.);
run;

proc print;
run;