Dynamic prompt

[rodrichiez]

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?

[Kurt_Bremser]

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.

[rodrichiez]

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, ^=, |, ||, ~=.  

[Tom]

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

[Kurt_Bremser]

---

@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.

[Tom]

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

[rodrichiez]

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

[Tom]

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

[rodrichiez]

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:

[Tom · Accepted solution]

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;

[rodrichiez]

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