Create a Valid Macro list ('A', 'B', 'C', etc) from the previous macro?

[ybz12003]

Hi Experts,

I would like to create a macro drop-list after the formatlist macro.   The new macro droplist could be used in the data steps for the further procedure. I use Proc SQL to generate a one, but log window showed an error message.  Please advice how to fix it, thanks.

%macro Answer(formatlist =); 

%let FormatCNT=%sysfunc(countw(&FormatList));                                                                                                       
                                                                                                                                        
%do JJ = 1 %to &FormatCNT;                                                                                                                    
	%let Format=%scan(&FormatList,&JJ); 
	proc sql;
	     select distinct &Format. from test;
	quit;
%end;
%mend;

%Answer(formatlist= A B C D E);

proc sql noprint;
	select Name into : droplist separated by ' ' from test
	where Name in ('&formatlist.');
quit;

%put &droplist;

data want; 
	 set test; 
	 if PUI_Access_Table not in ('&droplist.');
 run; 

[PaigeMiller]

You need to show us the data, and since you said the code does not work properly, please show us the ENTIRE log, not just the error messages, do not chop out parts of the log.

Please copy the log as text and paste it into the window that appears when you click on the {i} icon in your reply. This will help us because it formats the log to appear here exactly as you see it, and makes the log much more readable and understandable. DO NOT SKIP THIS STEP. If you skip this step, I will just ask for you to supply the LOG again following these directions.

[ybz12003]

187 proc sql noprint;

188 select Name into : droplist separated by ',' from export_a

189 where Name in ('&formatlist.');

ERROR: The following columns were not found in the contributing tables: Name.

190 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

WARNING: Apparent symbolic reference DROPLIST not resolved.

191

192 %put &droplist;

&droplist

[PaigeMiller]

Please copy the log as text and paste it into the window that appears when you click on the {i} icon in your reply. This will help us because it formats the log to appear here exactly as you see it, and makes the log much more readable and understandable. DO NOT SKIP THIS STEP. If you skip this step, I will just ask for you to supply the LOG again following these directions.

[Shmuel]

To use a macro avriable as a sas data list use double quotes instead single quotes :

where Name in ("&formatlist.");
.....

if PUI_Access_Table not in ("&droplist.");

[Tom]

Macro triggers are not evaluated inside of strings bounded by single quotes instead of double quotes.  So unless your NAME values contain actual ampersand characters you will not get any matches from:

where Name in ('&formatlist.')

But if you change it to using double quotes so that the macro variable reference is replaced by its value you are generating code like:

where Name in ("A B C D E")

which I also expect will not work.  If you want to use that list of unquoted values you will either need to convert it to a list of quoted values or use a different test.  Perhaps something like:

where findw("A B C D E",name,' ','i')

Which you could generate using:

where findw("&formatlist.",name,' ','i')

Now if you did want to use PROC SQL's INTO clause to generate a list of character values then use the QUOTE() function to add in quotes.

select quote(trim(Name))
  into : droplist separated by ' ' 
  from test

Then you will get values in DROPLIST that look something like:

"A" "C" "E"

And that you could use with the IN operator.

where PUI_Access_Table not in (&droplist);

[ybz12003]

Is it possible that I could just generate a macro drop-list with double quote? Maybe using either Proc SQL or Call Synput?

Droplist = A B C D

The final &Droplist.   is

"A", "B", "C", "D"

[Tom · Accepted solution]

---

@ybz12003 wrote:

Is it possible that I could just generate a macro drop-list with double quote? Maybe using either Proc SQL or Call Synput?

Droplist = A B C D

The final &Droplist.   is

"A", "B", "C", "D"

---

Please explain in different words more clearly what you are asking for.  

Why not just type the quotes when you type (or create) the original macro variable value?

Are you asking how to convert the existing value without the quotes into the value with the quotes?

If the original value has one and only one space between each "word" then you can do it with TRANWRD() function.

1124  %let var1=A B C;
1125  %let var2="%sysfunc(tranwrd(&var1,%str( )," "))";
1126  %put &=var1 &=var2;
VAR1=A B C VAR2="A" "B" "C"

Note you can use the COMPBL() function in insure that there is one and only one space between the values.

1130  %let var1=A  B   C;
1131  %let var2=%sysfunc(compbl(&var1));
1132  %put &=var1 &=var2;
VAR1=A  B   C VAR2=A B C

Do you really need commas instead of spaces after you have added the quotes?  How are you going to use the resulting macro variable? SAS does not need the commas in most places and commas in macro variables make them hard to pass as arguments to functions or macros.

[ybz12003]

Thank you so much!!!