Quoted spaces at end of Macro variable list

[frupaul]

Hi everyone,

I am working with an excel document attached below: This excel file is a categorical file that contains categorical variables which are not completely related. i.e. if you take a row and go across it from left to right, each new variable value is not related to the other. Data is:

My objective is to store each of these categorical fields in a separate macro variable list. The codes i have written are:

proc import datafile="/home/frupaul0/Carpenter/Demo1.xlsx" dbms=xlsx out=Categories;
run;

 proc sql noprint;
select
quote(trim(House)),
Portfolio,
quote(trim(City))
into :List_house separated by '',
:List_portfolio separated by '',
:List_city separated by ''
from Categories;
quit;

%put &List_city;

When the %put Statement is run, i notice some quoted spaces as shown blow:

How can I get rid of those quoted blanks at the end please?

[frupaul]

Sorry I posted without asking my question. How can I get rid of those quoted blanks? Is there any function that can help me achieve this?

[novinosrin]

you mean trailing blanks

use trim

quote(trim(House))

[frupaul]

Not really. I have just edited question because i realised i omitted the trim function. The problem is with the quoted blanks (as shown in the log) contained in the macro variable. Please refer to post

[novinosrin]

missing values???

should add a condition to ignore missing values perhaps, like

and not missing(var);

[frupaul]

That would work if i were only creating one variable list. In this case i am creating three variable list. Using that condition will drop out values for the other categorical variables

[novinosrin]

Can you please post a sample data with a few records of what you have and your requirement. So people can work with and offer you a solution

[frupaul]

Just adding in sample data as requested (excel file supporting this is included in original post):

[Reeza]

I strongly suspect you're building something better done with formats. 

Please consider looking into  it first before building these macro variable lists.

Either way, add a filter to the where clause to avoid selecting missing. 

where not missing(house);

or 

where not missing(city);

[Tom · Accepted solution]

Use a CASE statement to avoid adding quotes around missing values. 

Make a space delimited list instead of comma delimited list to avoid the extra commas.  SAS doesn't need commas between the values in the list.

proc sql noprint ;
 select case when missing(city) then city else quote(trim(city)) end
      , case when missing(house) then house else quote(trim(house)) end
  into :city_list separated by ' '
     , :house_list separated by ' '
  from test
 ;
quit;

 If you want to you can remove the trailing unquoted (and un-macro quoted) spaces with a simple %LET . But is it probably not needed.

%let city_list=&city_list;

[Astounding]

Just use 3 SELECT statements instead of 1.  Each can have its own WHERE clause, such as:

select quote(trim(House)) into :List_house separated by ' '  where house > ' '  from categories;

[frupaul]

I had that as an option. However the data i have posted on here only contains 3 Fields. The original data I am working with contains 35 Fields.

I was hoping there will be a direct way to accomplish that with one proc sql

[Astounding]

You still only need one PROC SQL.  Multiple SELECT statements are allowed.

It's probably easiest and clearest to write a small macro:

%macro onelist (varname);

select quote(trim(&varname)) into :List_&varname separated by ' '  where &varname > ' '  from categories;

%mend one_list;

Then you can go with:

proc sql;

%onelist (house)

%onelist (portfolio)

%onelist (city)

etc.

quit;

Less typing, definitely less error-prone.

[frupaul]

This sounds like a sensible approach. However, all these codes i have written are part of a macro I am building. Can i create a macro within a macro? That would be the solution if that is possible

[Astounding]

There's no need to create a macro within a macro.  (That would be bad practice.) 

Just create two macros.  One macro is allowed to call another macro.  The only requirement is that both macros have to exist when you actually execute the code.