topic Re: Dynamic prompt in SAS Programming

Dynamic prompt

rodrichiez — Wed, 18 Dec 2019 17:44:19 GMT

Hello,

I need some help. I'm trying to get the values generated by a dynamically populated prompt with multiple values, I'm using a macro but there is an error that I can't figure out:

              _______
              22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, 
              LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

The code is:

%let Fechas = &Period;

%macro obten_fecha ();
data lista_fechas;
format fmt_val DATE9.;
%do i=1 %to %sysfunc(countw("&Fechas."));
fmt_val = %scan((&Fechas.),&i);
output;
%end;
run;
%mend;
%obten_fecha;

Any help?

Re: Dynamic prompt

Kurt_Bremser — Wed, 18 Dec 2019 17:51:06 GMT

No macro needed:

data lista_fechas;
format fmt_val DATE9.;
do i = 1 to countw("&Fechas.");
  fmt_val = scan("&Fechas.",i);
  output;
end;
drop i;
run;

You may have to add the delimiter in the countw() and scan() functions if it is not one of the default delimiters of these functions.

Re: Dynamic prompt

rodrichiez — Wed, 18 Dec 2019 18:03:08 GMT

Hi @Kurt_Bremser

I'm still facing the same error.

Note: I'm working with dates values.

38         fmt_val = scan((&Fechas.),i);
NOTE: Line generated by the macro variable "FECHAS".
38         29Nov2019
             _______
             22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, 
              LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

Re: Dynamic prompt

Tom — Wed, 18 Dec 2019 18:17:04 GMT

You need to make sure your macro is generating valid SAS code.

fmt_val = scan((&Fechas.),i);

The scan function wants a valid character expression to scan. 20NOV2019 is not a variable name or a character literal. But you could convert it to one by adding quotes around it.

fmt_val = scan("&Fechas.",i);

Re: Dynamic prompt

Kurt_Bremser — Wed, 18 Dec 2019 18:20:23 GMT

@rodrichiez wrote:

Hi @Kurt_Bremser

I'm still facing the same error.

Note: I'm working with dates values.

38         fmt_val = scan((&Fechas.),i);
NOTE: Line generated by the macro variable "FECHAS".
38         29Nov2019
             _______
             22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, 
              LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

RUN MY CODE AS POSTED!

You need quotes around string literals.

Re: Dynamic prompt

Tom — Wed, 18 Dec 2019 18:29:53 GMT

Why do you add quotes in the macro expression (where they probably are not needed since they will become part of the value passed to the macro function) but not include them in the SAS expression (where they might be needed to force the compiler to realize the value is a character literal and not a number or the name of a variable).

What does the macro variable PERIOD contain? Does it contain numbers like 21883, which are valid date values? That seems to be what your code is assuming. Or does it contain strings that look to humans like dates? If the later are the strings in a format that the DATE informat would recognize?

Let's assume that the macro variable has string that look like 30NOV2019.

%macro obten_fecha (fechas);
data lista_fechas;
  format fmt_val DATE9.;
%do i=1 %to %sysfunc(countw(&Fechas.));
  fmt_val = "%scan(&Fechas.,&i)"d ;
  output;
%end;
  stop;
run;
%mend;

%let period=30NOV2019 31DEC2019;
%obten_fecha(&period);

Re: Dynamic prompt

rodrichiez — Wed, 18 Dec 2019 19:13:31 GMT

Hi @Tom

It is working now, but I don't know why is only taking the first value? Maybe it's something with the countw but Im not sure.

%macro obten_fecha (fechas);
data lista_fechas;
  format fmt_val DATE9.;
%do i=1 %to %sysfunc(countw(&Fechas.));
  fmt_val = "%scan(&Fechas.,&i)"d;
  output;
%end;
  stop;
run;
%mend;

%let Fechas=&Period;
%obten_fecha(&Fechas);

Dates selected:

17DEC2019

29NOV2019

30AUG2019

Result:

17DEC2019

Re: Dynamic prompt

Tom — Wed, 18 Dec 2019 19:17:49 GMT

What does PERIOD actually contain? How did you create it?

Re: Dynamic prompt

rodrichiez — Wed, 18 Dec 2019 19:46:23 GMT

Period is a dynamic prompt, where the user select from a list of values from the data source FECHAS_PROMPT. See attached images.

Period prompt:

Data source:

Re: Dynamic prompt

Tom — Wed, 18 Dec 2019 19:59:21 GMT

You need to figure out where the selected values actually get stored.

It used to place them into a series of macro variables. Like PERIOD1, PERIOD2, ... . and create another macro variable that had the number. Use %put _user_ to see all of the macro variables. I think the number might be in a macro variable named PERIOD0. But perhaps is is something else. You might also check what it does when user selects only one value instead of multiple values as that was also tricky.

So you probably need something like this instead:

%do i=1 %to &period0 ;
  fmt_val = "&&period&i"d ;
  output;
%end;

Re: Dynamic prompt

rodrichiez — Wed, 18 Dec 2019 20:06:05 GMT

Thank you @Tom ! It is working fine now. Thanks for your help. I figured out that the macro variable Period_count is the number of selected dates.

Final code:

%macro obten_fecha (fechas);
data lista_fechas;
  format fmt_val DATE9.;
%do i=1 %to &Period_count;
  fmt_val = "&&Period&i"d;
  output;
%end;
  stop;
run;
%mend;

%let Fechas=&Period;
%obten_fecha(&Fechas);