topic Have troubles with data type conversions in SAS Programming

Have troubles with data type conversions

IgorR — Thu, 16 Nov 2023 14:21:48 GMT

Hi,
I have this code, where I need to change manually only the variable "l_bal_date" and other variables are calculated by the program:


/* Current balance variables */
%let l_bal_date='01sep2023'd;
%let vMonth     = %sysfunc(Month(&l_bal_date), z2.);
%let vYear_2d   = %sysfunc(PUTN(&l_bal_date, year2.), 2.);
%let vYear_4d   = %sysfunc(PUTN(&l_bal_date, year4.), 4.);
%let maz_date   = &vmonth.&vYear_2d;
%let mazan_path = &vYear_4d.-&vMonth;
%let file       = new_&vYear_4d.&vMonth;
%let file_new   = &file;
%let file_date  = &vYear_4d.&vMonth;
%let date_re    = &file_date;
%let sheet_name = "&maz_date";

After that I have this code:

/*Previous balance Dates */
%Let Prev_bal_date  = intnx('month', &Prev_bal_date, -3, 'b');
%let vMonth_prev    = %sysfunc(Month(&Prev_bal_date), z2.);
%let vYear_prev_2d  = %sysfunc(PUTN(&Prev_bal_date, year2.), 2.);
%let vYear_prev_4d  = %sysfunc(PUTN(&Prev_bal_date, year4.), 4.);
%let prev_maz_date  = &vMonth_prev.&vYear_prev_2d;
%let file_name="\\Folder\results_sas_final_movement_&vMonth_prev.-&maz_date.";
%let file_name_movement="\\Folder\results_sas_final_movement_meforat_&vMonth_prev.-&maz_date.";

Second part of the code returns Error:

56         /*Previous balance Dates */
57         %Let Prev_bal_date  = intnx('month', &Prev_bal_date, -3, 'b');
58         %let vMonth_prev    = %sysfunc(Month(&Prev_bal_date), z2.);
ERROR: Argument 1 to function MONTH 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.
59         %let vYear_prev_2d  = %sysfunc(PUTN(&Prev_bal_date, year2.), 2.);
ERROR: Argument 1 to function PUTN 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.
60         %let vYear_prev_4d  = %sysfunc(PUTN(&Prev_bal_date, year4.), 4.);
ERROR: Argument 1 to function PUTN 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.
61         %let prev_maz_date  = &vMonth_prev.&vYear_prev_2d;
62         
63         %let file_name="\\Folder\results_sas_final_movement_&vMonth_prev.-&maz_date.";
64         %let file_name_movement="\\Folder\results_sas_final_movement_meforat_&vMonth_prev.-&maz_date.";

The Question:

What I'm doing wrong and what I have to do to get "file_name" and "file_name_movement" with dates instead of variable names?

Re: Have troubles with data type conversions

PaigeMiller — Thu, 16 Nov 2023 20:22:31 GMT

%Let Prev_bal_date  = intnx('month', &Prev_bal_date, -3, 'b');

Macro commands like %let cannot use INTNX, unless it is within %SYSFUNC. Also, within %SYSFUNC you don't put quotes around text. So this might work

%Let Prev_bal_date  = %sysfunc(intnx(month, &Prev_bal_date, -3, b));

but still there are problems since &prev_bal_date doesn't seem to have been assigned a value. So it won't work unless &prev_bal_date has been assigned a value (in code you have not shown us), and then &prev_bal_date MUST be a valid SAS date value.

Also, get out of the habit of treating dates as strings that have to be pulled apart and combined into desirable appearances.

%let maz_date   = &vmonth.&vYear_2d;

is an example of pulling a string apart and combining it, and is unnecessary. If you have a macro variable with a valid SAS date value, you can obtain MMYY with the proper format applied to the macro variable with a valid SAS date value. So this would work

%let l_bal_date=%sysevalf('01sep2023'd); /* &L_BAL_DATE is a valid SAS date */
%let maz_date=%sysfunc(putn(&l_bal_date,mmyyn4.)); /* Use format on valid SAS date value */

and now &maz_date is equal to 0923 without pulling strings apart and recombining them.

Re: Have troubles with data type conversions

Tom — Thu, 16 Nov 2023 17:08:56 GMT

You passed the MONTH() function a string that started with the letters intnx. It was expecting a numeric date value.

I suspect you wanted the macro processor to actually run the INTNX() function instead of just building a string that could by used to generate SAS code that would run that function.

You already appear familiar with using %SYSFUNC() to run SAS functions in macro code, so just do it here also.

%Let Prev_bal_date  = %sysfunc(intnx(month, &Prev_bal_date, -3, b));

Re: Have troubles with data type conversions

IgorR — Mon, 20 Nov 2023 07:27:58 GMT

Thank you!

It was very helpful.

The purpose of the code I provided is to build names of files to export data from my datasets (in very old SAS program that I'm trying to improve step by step).

Till now I had to input every file name manually, and now I'm doing it automatically, based on single variable with date type.

But I still confused about difference between:

%let Var = 0923 and %let Var = "0923".

Which format is preferable to use within strings?

Re: Have troubles with data type conversions

Tom — Mon, 20 Nov 2023 14:03:01 GMT

@IgorR wrote:

Thank you!

It was very helpful.

The purpose of the code I provided is to build names of files to export data from my datasets (in very old SAS program that I'm trying to improve step by step).

Till now I had to input every file name manually, and now I'm doing it automatically, based on single variable with date type.

But I still confused about difference between:

%let Var = 0923 and %let Var = "0923".

Which format is preferable to use within strings?

It depends on how you want to USE the value the of the macro variable.

If you want to use it directly to generate part of string, such when building a filename, then you don't want the quotes in the value.

%let var=0923;
filename indata "/mydir/myprefix&var..txt";

If you want to use to generate SAS code then the quotes might make it easier. Since the quoted digits will look like a string literal to SAS, but the unquoted digits would look like a number.

%let var="0923";
data want;
  length fname $200;
  fname=cats('/mydir/myprefix',&var,'.txt');
  infile indate filevar=fname ;
...