topic Adding a variable to a Title in SAS Programming

Adding a variable to a Title

hobus — Thu, 15 Sep 2022 22:08:11 GMT

I am trying to automate a title to include information from a table into a title. So I am using a macro to define the selection criteria that I am given.

%let id=123456

Then in the report I need to call that id# and return 2 specific pieces of information from the table. In an sql statement it would be written as:

select name, filenumber

from table1
where id=&id

But I need to take the name and filenumber and put these into the title.

Title1 "Report for &name &filenumber"

This of course doesn't work but is the closest information I have ben able to find.

Re: Adding a variable to a Title

Reeza — Thu, 15 Sep 2022 22:29:56 GMT

proc sql noprint;
select name, filenumber into :m_name, :m_fileNumber
from table1
where id=&id;
quit;

title1 "Report for &m_name &m_fileNumber";

Something like that?

@hobus wrote:

I am trying to automate a title to include information from a table into a title. So I am using a macro to define the selection criteria that I am given.

%let id=123456

Then in the report I need to call that id# and return 2 specific pieces of information from the table. In an sql statement it would be written as:

select name, filenumber

from table1
where id=&id

But I need to take the name and filenumber and put these into the title.

Title1 "Report for &name &filenumber"

This of course doesn't work but is the closest information I have ben able to find.

%let name = Alfred;
proc sql noprint;
select age, sex into :m_age, :m_sex from sashelp.class where name = "&name";
quit;

%put &m_age.;
%put &m_sex.;

Title1 "Report for &name, Age = &m_age, Sex = &m_sex";
proc print data=sashelp.class noobs label;
where name = "&name";
run;

Re: Adding a variable to a Title

ballardw — Thu, 15 Sep 2022 22:44:38 GMT

IF, and that is a big if in many cases, the Name and Filenumber values you want have a single one value for the given id:

INTO is the instruction in SQL to place values into macro variables. Also not the use of : preceding the name of the macro variable. The distinct may be overkill but if you have multiple records with the same name and filenumber. I used different macro variable names so you can see where the macro variable appears easier. Data set variables and macro variables my get confused at some point and having different names sometimes helps follow code instead of "Name" appear in many places.

%let id=123456;

Proc sql noprint;
   select distinct name, filenumber into : namevar, : numvar
   from table1
   where id = &id.;
quit;

Title1 "Report for &namevar. &numvar.";

If a variable is truly numeric you may want to control the conversion with a Put(numericvar, <someformat> -L) . The default conversion that SAS would use is a best12. format and the result will be right justified which can place a lot of spaces before your variable.

You can see that in this example where the first Age has a bunch of spaces in the output.

%let id=Alice;

Proc sql noprint;
   select distinct sex, age, put(age,best5. -L) into: namevar, :numvar1, :numvar2
   from sashelp.class
   where name = "&id.";
quit;

%put Namevar=&namevar  numvar1=&numvar1 numvar2=&numvar2;