BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
IgorR
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
%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.

--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26
%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.

--
Paige Miller
IgorR
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

@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 ;
...
Tom
Super User Tom
Super User

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));

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 334 views
  • 2 likes
  • 3 in conversation