topic Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line in ODS and Base Reporting

Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Wed, 23 Dec 2020 17:38:55 GMT

I have a ODS proc report that uses values from a multi-value prompt. I need to display all values selected for each prompt in the Title statments. Using the method below, it only produces the first selction and ignores the rest for the title statments. Any advise on how to get it to display all values selected for each prompt.

Example: Prompt2: User selects A, B, andC from list. On Title statement it shows Prompt2: A instead of Prompt2: A B C.

Title font=arial height=16pt color=bib justify=center "Prompt1: &Prompt1" ;

Title2 font=arial height=12pt color=bib justify=center "Prompt2: &Prompt2" ;

Title3 font=arial height=12pt color=bib justify=center "Prompt3: &Prompt3" ;

Title4 font=arial height=12pt color=bib justify=center "Prompt4: &Prompt4" ;

Title5 font=arial height=12pt color=bib justify=center "Prompt5: &Prompt5" ;

Title6 font=arial height=12pt color=bib justify=center "Prompt6: &Prompt6" ;

Title7 font=arial height=12pt color=bib justify=center "Prompt7: &Prompt7" ;

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Shmuel — Wed, 23 Dec 2020 17:53:48 GMT

What are the variables (or macro variables) that contain the selected variables from the list?

Suppose they are &var1, &var2, &var3.

The title should include a code like: "...promted vars are: &var1, &var2, &var3 ...";

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Cynthia_sas — Wed, 23 Dec 2020 18:24:41 GMT

Hi:

This worked for me, so perhaps there is something else going on with how your prompt values are being passed to the program. When I used %LET statements, everything worked well, as shown below:

Cynthia

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Thu, 24 Dec 2020 13:57:43 GMT

Thank you. I realize now that the variable are numbered after the prompt name. Do you know how to only make it display if not blank? I won't know how many they are going to select each time, maybe 1 or maybe 10. Example:

%LET MODEL_count = 2;
%LET MODEL = Volvo;
%LET MODEL2 = Ford;
%LET MODEL1 = Volvo;
%LET MODEL0 = 2;

Title "Vehicle Type: &MODEL &Model2 &Model3";

Results:

Vehicle Type: Volvo Ford &Model3

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Shmuel — Thu, 24 Dec 2020 16:03:03 GMT

Does &model_count tells how many models you have.

If positive you can create the TITLE either in a macro program or by a data step:

data _NULL_;
   length titl $100; /* assuming max length 10*10 chars */
   do i=1 to &model_count;
       titl = catx(' ',titl,"&&model&i");
  end;
  call symput('titl',strip(titl));
run;

OR by

%let TITL =;
%do i=1 %to &model_count;
       %let TITL =&TITL.%str( )&&model&i. ;
%end;

Finally the TITLE statement:

TITLE ... "&titl";

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Mon, 28 Dec 2020 15:35:57 GMT

Thank you for the help. This is what I get when I tried.

33 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
34 FILENAME EGXLSSX TEMP;
35 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel
36 OPTIONS (
37 EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no"
38 );
39
40 GOPTIONS ACCESSIBLE;
41 ods graphics on / reset=all;
42 %let TITL =;
43 %do i=1 %to &model_count;
ERROR: The %DO statement is not valid in open code.
44 %let TITL =&TITL.%str( )&&model&i. ;
WARNING: Apparent symbolic reference I not resolved.
WARNING: Apparent symbolic reference I not resolved.
45 %end;
ERROR: The %END statement is not valid in open code.
46
47 Title "Vehicle Type: &Titl";
WARNING: Apparent symbolic reference I not resolved.

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Shmuel — Mon, 28 Dec 2020 16:03:40 GMT

According to the error message "ERROR: The %DO statement is not valid in open code."

you need to modify the code into:


%let ttitl=;
%macro create_titles;
   %do i=1 %to &model_count;
      %let TITL&i = &TITL.%str( )&&model&i. ;
   %end;
%mend;
%create_titles;

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Mon, 28 Dec 2020 17:15:42 GMT

Thanks again for the help. I'm battling a migraine and my brain isn't trying to think today. However, when I changed out the code, I not get a Title of (see image). It just leaves the value blank instead of displaying the two car models. This is the code I'm using.

ods graphics on / reset=all;

%let titl=;
%macro create_titles;
%do i=1 %to &model_count;
%let TITL&i = &TITL.%str( )&&model&i. ;
%end;
%mend;
%create_titles;
Title "Vehicle Type: &Titl";

proc report data=WORK.QUERY_FOR_CARS;
run;

quit;

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Shmuel — Mon, 28 Dec 2020 17:39:22 GMT

Hope you fill better now.

As you have several titles, just add a line to the macro:

ods graphics on / reset=all;

%let titl=;
%macro create_titles;
%do i=1 %to &model_count;
%let TITL&i = &TITL.%str( )&&model&i. ;
&&title&i;;  /* line added */
%end;
%mend;
%create_titles;
Title "Vehicle Type: &Titl";

proc report data=WORK.QUERY_FOR_CARS;
run;

quit;

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Wed, 30 Dec 2020 13:59:55 GMT

I fixed Title to be Titl, but I still get this error message. It's resolving them, but creating an error. I've looked for a missing semicolon, but not aware of where else I should have one.

287        QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

288        /* --- End of code for "Query Builder". --- */
289        
290        /* --- Start of code for "Program". --- */
291        ods graphics on / reset=all;
292        
293        %let titl=;
294        %macro create_titles;
295        %do i=1 %to &model_count;
296        %let TITL&i = &TITL.%str( )&&model&i. ;
297        &&titl&i;;  /* line added */
298        %end;
299        %mend;
300        %create_titles;

                                                                                          The SAS System

NOTE: Line generated by the macro variable "TITL1".300          Honda
             _____
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the macro variable "TITL2".300          Volvo
             _____
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.301        Title "Vehicle Type: &Titl";
302        
303        proc report data=WORK.QUERY_FOR_CARS;
304        run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      305        
306        quit;
307        /* --- End of code for "Program". --- */

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Tom — Wed, 30 Dec 2020 15:26:51 GMT

That DATA step is not going to work. You seem to have mixed up usage of variables and macro variables. Use the data step variable to generate the name of the macro variable.

data _null_;
   length titl $100; /* assuming max length 10*10 chars */
   do i=1 to &model_count;
       titl = catx(' ',titl,symget(cats('model',i)));
  end;
  call symputx('titl',titl);
run;

And for a macro solution it will probably be useful to use a "function style" macro. That is one that just generates part of a single statement.

%macro cat_prompt(prompt);
%local i;
%if %symexist(&prompt._count) %then %do;
  %do i=1 %to &&&prompt._count; &&&prompt.&i %end;
%end;
%mend cat_prompt;

title1 "%cat_prompt(model)";

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

Shmuel — Wed, 30 Dec 2020 15:57:20 GMT

Sorry for the mass. Next code is tested:

%let titl=;
%macro create_titles;
	%do i=1 %to &model_count;
		%let TITL&i = Title&i "&&model&i." ;
		&&titl&i;;  /* line added */
	%end;
%mend;
%create_titles;

You can add any style or text in the %let TITL&i = Title&i "&&model&i." ;

Use options mprint to check how is &&titl&i resolved.

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Wed, 30 Dec 2020 21:32:46 GMT

Hi. Thank you for the input. These options work great when two or more options are selected. However, when one option or ALL_Possible_VALUES is selected in the prompt, the proc report title becomes blank. Are you able to provide any help on that?

When ALL_Possible_Values is selected in prompt, this is the error.

NOTE: Invalid argument to function SYMGET('model1') at line 46 column 29.
titl= i=2 _ERROR_=1 _N_=1
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Wed, 30 Dec 2020 21:38:13 GMT

From log:
%LET MODEL_count = 1;
%LET MODEL = BMW;

AND
%LET MODEL_count = 1;
%LET MODEL = _ALL_VALUES_;