topic Re: YYMMN6 format variable conversion to character in SAS Programming

YYMMN6 format variable conversion to character

Jumboshrimps — Wed, 29 Jun 2022 20:44:30 GMT

Have to create a variable to be used in multiple SQL statements in format YYMMN6.

Need current month in this format and previous month.

%let this_month =%sysfunc(date(), yymmn6.) ; /current month in YYMMN6.
%put &this_month.;

data _null_;
Format last_mnth yymmn6.;
format lst_mnth $6.;
format char_mnth $6.;
last_month = intnx('month',today(),-1,'E');
call symput("last_mnth", put (last_month, YYMMN6.));
%put &last_mnth.;
run;

Code gives me last month in correct format, but as a numeric and variable in dataset is character so getting the error message below when attempting to run SQL:

LOG:

SYMBOLGEN: Macro variable THIS_MNTH resolves to 202206 /*all good, current month
31 where yrmo = &last_mnth.; /where cause in PROC SQL
SYMBOLGEN: Macro variable LAST_MNTH resolves to 202205 /*all good, last month
ERROR: Expression using equals (=) has components that are of different data types.

How do I convert variable "last_mnth" to a character in the macro, so I can use it throughout the remaining code?

Thank you

Re: YYMMN6 format variable conversion to character

Reeza — Wed, 29 Jun 2022 20:47:30 GMT

In this case I think you just need quotes.

where yrmo = "&last_mnth.";

Re: YYMMN6 format variable conversion to character

PaigeMiller — Wed, 29 Jun 2022 20:55:11 GMT

@Jumboshrimps wrote:

LOG:

SYMBOLGEN: Macro variable THIS_MNTH resolves to 202206 /*all good, current month
31 where yrmo = &last_mnth.; /where cause in PROC SQL
SYMBOLGEN: Macro variable LAST_MNTH resolves to 202205 /*all good, last month
ERROR: Expression using equals (=) has components that are of different data types.

The ERROR indicates YRMO is character, normally you wouldn't store calendar information as character variables ... but let me ask you a simple question.

If you have YRMO as a character variable and you want to test to see if it is a specific value, please finish this WHERE statement without macro variables, what should it look like:

WHERE YRMO = _______________ ; (you fill in the blank with something that will not generate a syntax error)

Re: YYMMN6 format variable conversion to character

ErikLund_Jensen — Wed, 29 Jun 2022 21:45:06 GMT

Hi @Jumboshrimps

Remember that a macro variable is not defined with a data type (char or num). The macro variable has a content, and when the variable is resolved, the content is inserted instead, so it works the same way as if the value was written from the keyboard.

If the variable resolves to digits only, it can be used without quotes in places where a number is legal syntax, but if the content calls for a character value, ex.in a comparison with a char variable in a where statement, the value should be written in quotes, so the macro variable must be enclosed in double quotes (double, because it will not resolve in single quotes).

Re: YYMMN6 format variable conversion to character

Tom — Thu, 30 Jun 2022 01:04:06 GMT

From the error message YRMO variable is character and you tried to compare it to the number 202,205.00 instead of the string '202205' because you ran this code:

where yrmo = 202205;

instead of

where yrmo = "202205";

Your data step is also confused.

You are running the %PUT to echo the values of LAST_MNTH before the data step has had a chance to create it.

data _null_;
  last_month = intnx('month',today(),-1,'E');
  call symputx("last_mnth", put(last_month, YYMMN6.));
run;

%put &=last_mnth;

Re: YYMMN6 format variable conversion to character

andreas_lds — Thu, 30 Jun 2022 04:51:57 GMT

data _null_;
Format last_mnth yymmn6.;
format lst_mnth $6.;
format char_mnth $6.;
last_month = intnx('month',today(),-1,'E');
call symput("last_mnth", put (last_month, YYMMN6.));
%put &=last_mnth.;
run;

The %put-statement won't work as expected, because it is resolved before the data step is executed. From the second program execution you will see the value of the prior execution. Moving the %put statement after run; solves the problem.