topic Re: Quoted spaces at end of Macro variable list in SAS Programming

Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 19:00:14 GMT

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?

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 18:38:59 GMT

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?

Re: Quoted spaces at end of Macro variable list

novinosrin — Tue, 24 Jul 2018 18:42:15 GMT

you mean trailing blanks

use trim

quote(trim(House))

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 18:45:40 GMT

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

Re: Quoted spaces at end of Macro variable list

novinosrin — Tue, 24 Jul 2018 18:46:09 GMT

missing values???

should add a condition to ignore missing values perhaps, like

and not missing(var);

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 18:53:59 GMT

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

Re: Quoted spaces at end of Macro variable list

novinosrin — Tue, 24 Jul 2018 18:55:16 GMT

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

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 18:57:46 GMT

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

Re: Quoted spaces at end of Macro variable list

Astounding — Tue, 24 Jul 2018 19:00:45 GMT

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;

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 19:03:39 GMT

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

Re: Quoted spaces at end of Macro variable list

SuryaKiran — Tue, 24 Jul 2018 19:07:19 GMT

Not in a single select statement since you have missing values. Use different select statements

data test;
infile datalines dlm=',' dsd missover;
input House $ Portfolio $ City $;
datalines;
eson Str,1,London
Portabello,2,Peru
Munopolis,3,Lisbon
duncaster,4,New York
,5,Los Angeles
,6,
,7,
,8,
;
run;

 proc sql ;
select quote(trim(House)) into:List_house separated by ''
	from test
	where House is not null;
select quote(trim(Portfolio)) into:List_portfolio separated by ''
	from test
	where Portfolio is not null;
select quote(trim(City)) into:List_city separated by ''
	from test
	where City is not null;
quit;

%put &List_house;
%put &List_portfolio;
%put &List_city;

Re: Quoted spaces at end of Macro variable list

Astounding — Tue, 24 Jul 2018 19:08:12 GMT

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.

Re: Quoted spaces at end of Macro variable list

frupaul — Tue, 24 Jul 2018 19:26:57 GMT

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

Re: Quoted spaces at end of Macro variable list

Reeza — Tue, 24 Jul 2018 19:27:14 GMT

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

Re: Quoted spaces at end of Macro variable list

Astounding — Tue, 24 Jul 2018 19:34:00 GMT

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.

Re: Quoted spaces at end of Macro variable list

Tom — Tue, 24 Jul 2018 21:39:08 GMT

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;