topic Macro list issue - Proc SQL seperate by ' ' ? in SAS Programming

Macro list issue - Proc SQL seperate by ' ' ?

ybz12003 — Wed, 07 Nov 2018 19:15:59 GMT

Hi all:

I created a macro list by Proc SQL. However, I found there are space between the ID and could not be recognized in Where statement. For example, ID is 'DCT00025', 'DEY00056', 'DWR00047', etc. After the macro, it became ''DCT00025 ', 'DEY00056 ', 'DWR00047 '. Please advice how to solve this problem. Thanks.

proc sql noprint;
	select ID into : Unmatchlist separated by " ',' " from Unmatch;
quit;
 
%put &Unmatchlist;

data Want;
	set Test (keep = ID SITE DATE);
	where ID in ('&Unmatchlist');
run;

Then I got my macro Unmatch list like this:

1037 %put &Unmatchlist;

DCT00025 ', ' DEY00056 ', ' DWR00047

Which makes them unrecognized in the where statement.

Re: Macro list issue - Proc SQL seperate by ' ' ?

Kurt_Bremser — Wed, 07 Nov 2018 19:20:56 GMT

Do it in one SQL:

proc sql;
create table want as
select id, site, date
from test
where id in (select id from unmatch);
quit;

Re: Macro list issue - Proc SQL seperate by ' ' ?

Peter_C — Wed, 07 Nov 2018 19:24:10 GMT

Select ID format= $quote100. Into unmatchList separated by ', '
From unmatch

The $quote format applies the trimming

Re: Macro list issue - Proc SQL seperate by ' ' ?

Peter_C — Wed, 07 Nov 2018 19:26:42 GMT

I should add the usage of the macro variable needs no quotes:
Where ID in( &unmatchList)

Re: Macro list issue - Proc SQL seperate by ' ' ?

novinosrin — Wed, 07 Nov 2018 19:31:04 GMT

data unmatch;
input id $10.;
cards;
DCT00025
DEY00056
DWR00047
;
proc sql noprint;
	select quote(strip(ID)) into : Unmatchlist  separated by  ','   from Unmatch;
quit;
 
%put &Unmatchlist;

data Want;
	set Test (keep = ID SITE DATE);
	where ID in (&Unmatchlist);
run;

Re: Macro list issue - Proc SQL seperate by ' ' ?

Peter_C — Wed, 07 Nov 2018 19:57:56 GMT

Storing leading blanks seem unnatural to me, in the SAS environment.
Hence I never think of using STRIP() function.
And I should test whether the $quote format performs STRIP or TRIM in my SQL code above

Re: Macro list issue - Proc SQL seperate by ' ' ?

ybz12003 — Wed, 07 Nov 2018 20:58:59 GMT

Hi KurtBremser:

I think the Q is answered more closed by Novinosrin, although your code is definitely more directly and simple.