Hi @Jeff_DOC , base on your question and @Tom 's instruction, my solution is as follows. Please kindly let me if it answer your question @Jeff_DOC , many thanks! data timeraw1; input refdate mmddyy10. idnum tripd mmddyy10. tript returnd mmddyy10. returnt; format refdate tripd returnd mmddyy10. tript returnt time.; datalines; 01/01/2018 636 7/11/2009 1416 7/11/2009 1950 02/01/2018 636 7/11/2009 1416 7/12/2009 1950 ; run; proc print data=timeraw1;run; data timediff; set timeraw1; tripdt=dhms(tripd,0,0,tript); returndt=dhms(returnd,0,0,returnt); diff=returndt-tripdt; format tripdt returndt datetime19. diff time12.; run; proc print data=timediff;run; ... View more

Hi @thanikondharish , this is a solution using data step (not sql) base on the codes, suggestions, and comments from others in previous threads: data hw; input Name $ Sex $ Age Height Weight; datalines; Alice F 13 56.5 84 Barbara F 13 65.3 98 Carol F 14 62.8 102.5 Jane F 12 59.8 84.5 Janet F 15 62.5 112.5 Joyce F 11 51.3 50.5 Judy F 14 64.3 90 Louise F 12 56.3 77 Mary F 15 66.5 112 Alfred M 14 69 112.5 Henry M 14 63.5 102.5 James M 12 57.3 83 Jeffrey M 13 62.5 84 John M 12 59 99.5 Philip M 16 72 150 Robert M 12 64.8 128 Ronald M 15 67 133 Thomas M 11 57.5 85 William M 15 66.5 112 ; run; proc print data=hw;run; data hwseq; set hw; by sex; seq+1; if first.sex then seq=1; run; proc print data=hwseq;run; ... View more

Hi @Feefee does this answer your question? data personcd; input id location $5-26 person1 $28-42 person2 $44-62 person3 $64-74 p1code $5. p2code $5. p3code $5.; datalines; 101 Hampersmith,Silershine Sam Copewell Amy Baker John Aston 123A 234B 345C 102 Coleridge,Brisbake Claude Tremaine Marissa Carter 567D 678E 103 Waterloop,Sturrey Brian Amble Charlie Springwater Mandy Smith 012G 134H 245J ; run; proc print data=personcd;run; proc transpose data=personcd out=name(rename=(col1=person) drop=_name_); by id location; var person:; run; proc print data=name;run; proc transpose data=personcd out=code(rename=(col1=personcd) drop=_name_); by id location; var p1code--p3code; run; proc print data=code;run; data final; merge name code; if person^=' '; run; proc print data=final;run;   ... View more

Hi @Jan_Jensen , does this answer your question? The code and result are as follows: data itemfl1; input itemid itemid_ref; datalines; 1111 . 2222 8888 3333 . 4444 1111 5555 . 6666 7777 7777 . ; run; proc sql noprint; select distinct itemid into :itemidlist separated by ',' from itemfl1; quit; data itemfl2; set itemfl1; if itemid_ref in (&itemidlist) then flag=1; else flag=0; run; proc print data=itemfl2;run; ... View more

Hi @Batman , when you use z. format to display a number, it will be displayed in a way like this: 1) the number will start with several 0, depending on how many digits the z. format has, 2) to determine how many digits the z. format has, use a number after z before the dot ., for example, z5. means it has five digits with several 0 before your number, if your number has 2 digits, it will has three 0 before it, if you number has 4 digits, it will has one 0 before it, and 3) if you use z5. for a number longer than 5 digits, it won't work. So to solve your question, you need a number larger than 5 in the z. format, for example, z10., which gives more space to display your number. The code and output is as follows: data sample; input num_zip 8.; format num_zip 9.; datalines; 23 7777777 999999999 ; run; data convert; set sample; zipcode=put(num_zip,z10.); run; proc print data=convert noobs; var zipcode; run; ... View more

Hi @Tom thanks a lot for your reply and comments! This is what I think:   1) My answer was not an exercise of select into :list; statement. I wrote "I took example in SAS Macro1: essentials course note pdf as my reference code", by that I mean I learnt the select into :list statement and technique from the material, I was not saying all parts of my answer was from the material.   2) I came up with my solution like this:   2.1) The original post asks "how to get a comma separated employee list using proc sql?" This is a typical question that can be solved through proc sql; select into :list statement.   2.2) the original post asks "how to get a comma separated employee list for each department?" To answer this part of the question, one need to add a where; statement in the proc sql; select into :list; step, and the where statement specifies something like where deptvar="deptmentname" ;. One can repeat the proc sql; select into :list  step several times and use different department values in the where; statement. Since the step need to be repeated, creating macro variables that has a list of values for the department is a proper method to use, and this leads to another proc sql; select into :list step, which creates the department value list. One can create the macro variable list through data _null_; and call symputx();, but the proc sql; select into :list came to my mind first so I used that one. ... View more

Hi @pavank does this answer your question? (Please omit the proc sql; step, that step was just to see if the v1,v2,v3 tables generated correctly, you do not need the proc sql; step to generate the final output) data v_sort; input v1$ v2$ v3$; cards; A B C F K J D E F C K J K L W S V R ; run; data v1(keep=v1) v2(keep=v2) v3(keep=v3); set v_sort; run; proc sql noprint; select * from v1; select * from v2; select * from v3; quit; proc sort data=v1;by v1;run; proc sort data=v2;by v2;run; proc sort data=v3;by v3;run; data final; merge v1 v2 v3; run; proc print data=final;run; The repeated data;set; and proc sort; steps can be replaced by a macro, like this (the code, log, and result are as follows): (please omit the proc sql; step, that step was just to see if the tables v1,v2,v3 generated correctly, you do not need the proc sql; step for the final table) data v_sort; input v1$ v2$ v3$; cards; A B C F K J D E F C K J K L W S V R ; run; %macro sortvars; %do i=1 %to 3; data v&i(keep=v&i); set v_sort; run; proc sql noprint; select * from v&i; quit; proc sort data=v&i;by v&i;run; %end; %mend sortvars; options symbolgen mlogic mprint; %sortvars; options nosymbolgen nomlogic nomprint; data final; merge v1 v2 v3; run; proc print data=final;run; 69 data v_sort; 70 input v1$ v2$ v3$; 71 cards; NOTE: The data set WORK.V_SORT has 6 observations and 3 variables. 78 ; 79 run; 80 %macro sortvars; 81 %do i=1 %to 3; 82 data v&i(keep=v&i); 83 set v_sort; 84 run; 85 proc sql noprint; 86 select * from v&i; 87 quit; 88 proc sort data=v&i;by v&i;run; 89 %end; 90 %mend sortvars; 91 options symbolgen mlogic mprint; 92 %sortvars; MLOGIC(SORTVARS): Beginning execution. MLOGIC(SORTVARS): %DO loop beginning; index variable I; start value is 1; stop value is 3; by value is 1. SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable I resolves to 1 MPRINT(SORTVARS): data v1(keep=v1); MPRINT(SORTVARS): set v_sort; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V_SORT. NOTE: The data set WORK.V1 has 6 observations and 1 variables. MPRINT(SORTVARS): proc sql noprint; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(SORTVARS): select * from v1; MPRINT(SORTVARS): quit; NOTE: PROCEDURE SQL used (Total process time): SYMBOLGEN: Macro variable I resolves to 1 MPRINT(SORTVARS): proc sort data=v1; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(SORTVARS): by v1; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V1. NOTE: The data set WORK.V1 has 6 observations and 1 variables. MLOGIC(SORTVARS): %DO loop index variable I is now 2; loop will iterate again. SYMBOLGEN: Macro variable I resolves to 2 SYMBOLGEN: Macro variable I resolves to 2 MPRINT(SORTVARS): data v2(keep=v2); MPRINT(SORTVARS): set v_sort; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V_SORT. NOTE: The data set WORK.V2 has 6 observations and 1 variables. MPRINT(SORTVARS): proc sql noprint; SYMBOLGEN: Macro variable I resolves to 2 MPRINT(SORTVARS): select * from v2; MPRINT(SORTVARS): quit; NOTE: PROCEDURE SQL used (Total process time): SYMBOLGEN: Macro variable I resolves to 2 MPRINT(SORTVARS): proc sort data=v2; SYMBOLGEN: Macro variable I resolves to 2 MPRINT(SORTVARS): by v2; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V2. NOTE: The data set WORK.V2 has 6 observations and 1 variables. MLOGIC(SORTVARS): %DO loop index variable I is now 3; loop will iterate again. SYMBOLGEN: Macro variable I resolves to 3 SYMBOLGEN: Macro variable I resolves to 3 MPRINT(SORTVARS): data v3(keep=v3); MPRINT(SORTVARS): set v_sort; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V_SORT. NOTE: The data set WORK.V3 has 6 observations and 1 variables. MPRINT(SORTVARS): proc sql noprint; SYMBOLGEN: Macro variable I resolves to 3 MPRINT(SORTVARS): select * from v3; MPRINT(SORTVARS): quit; SYMBOLGEN: Macro variable I resolves to 3 MPRINT(SORTVARS): proc sort data=v3; SYMBOLGEN: Macro variable I resolves to 3 MPRINT(SORTVARS): by v3; MPRINT(SORTVARS): run; NOTE: There were 6 observations read from the data set WORK.V3. NOTE: The data set WORK.V3 has 6 observations and 1 variables. MLOGIC(SORTVARS): %DO loop index variable I is now 4; loop will not iterate again. MLOGIC(SORTVARS): Ending execution. 93 options nosymbolgen nomlogic nomprint; 94 data final; 95 merge v1 v2 v3; 96 run; NOTE: There were 6 observations read from the data set WORK.V1. NOTE: There were 6 observations read from the data set WORK.V2. NOTE: There were 6 observations read from the data set WORK.V3. NOTE: The data set WORK.FINAL has 6 observations and 3 variables. ... View more

Hi @pavank does this answer your question? data testy; input a; datalines; 1 2 3 4 5 6 7 8 9 ; run; proc print data=testy;run; proc sql noprint; select distinct a into :avalue1- from testy; quit; %put &avalue1 &avalue9; data testy2; set testy; if _n_=1 then a=&avalue9; if _n_=9 then a=&avalue1; run; proc print data=testy2;run;   ... View more

Hi @pavank does this answer your question? My code and result is as follows (the code I take as my reference code is in SAS Macro1: essentials course note pdf): data employees; input department $ employee $; datalines; Sales John Sales Mary HR Alice HR Bob IT Eve IT Charlie ; run; proc sql; select distinct department into :deptlist1- from employees; quit; %put &sqlobs;/*3*/ %put &deptlist1 &deptlist2 &deptlist3; %macro emp; %do i=1 %to 3; proc sql noprint; select distinct employee into :emplist separated by ',' from employees where department="&&deptlist&i"; quit; %put &emplist; %end; %mend emp; %emp; 69 data employees; 70 input department $ employee $; 71 datalines; NOTE: The data set WORK.EMPLOYEES has 6 observations and 2 variables. 78 ; 79 run; 80 proc sql; 81 select distinct department 82 into :deptlist1- 83 from employees; 84 quit; NOTE: PROCEDURE SQL used (Total process time): 85 %put &sqlobs;/*3*/ 3 86 %put &deptlist1 &deptlist2 &deptlist3; HR IT Sales 87 %macro emp; 88 %do i=1 %to 3; 89 proc sql noprint; 90 select distinct employee 91 into :emplist separated by ',' 92 from employees 93 where department="&&deptlist&i"; 94 quit; 95 %put &emplist; 96 %end; 97 %mend emp; 98 %emp; NOTE: PROCEDURE SQL used (Total process time): Alice,Bob NOTE: PROCEDURE SQL used (Total process time): Charlie,Eve NOTE: PROCEDURE SQL used (Total process time): John,Mary 99 100 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; ... View more

I forgot to delete the minoperator options when use %index, the code which use %index and without the minoperator is as follows: %macro customerlist(ctry); proc sql noprint; select distinct country into :ctrylist separated by " " from mc1.customers; quit; %if %index(&ctrylist,&ctry) %then %do; title "customers from &ctry"; proc sgplot data=mc1.customers; vbar group; yaxis grid; where country="&ctry"; run; %end; %else %do; %put ERROR: &ctry is an invalid country code.; %put ERROR: Valid country codes include &ctrylist..; %end; %mend customerlist; options symbolgen mprint mlogic; %customerlist(AU); %customerlist(zz); options nosymbolgen nomprint nomlogic; SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA SYMBOLGEN: Macro variable CTRY resolves to AU MLOGIC(CUSTOMERLIST): %IF condition %index(&ctrylist,&ctry) is TRUE SYMBOLGEN: Macro variable CTRY resolves to AU MPRINT(CUSTOMERLIST): title "customers from AU"; MPRINT(CUSTOMERLIST): proc sgplot data=mc1.customers; MPRINT(CUSTOMERLIST): vbar group; MPRINT(CUSTOMERLIST): yaxis grid; SYMBOLGEN: Macro variable CTRY resolves to AU MPRINT(CUSTOMERLIST): where country="AU"; MPRINT(CUSTOMERLIST): run; NOTE: There were 90 observations read from the data set MC1.CUSTOMERS. WHERE country='AU'; MLOGIC(CUSTOMERLIST): Ending execution. 90 %customerlist(zz); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value zz MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by " " from mc1.customers; MPRINT(CUSTOMERLIST): quit; SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA SYMBOLGEN: Macro variable CTRY resolves to zz MLOGIC(CUSTOMERLIST): %IF condition %index(&ctrylist,&ctry) is FALSE MLOGIC(CUSTOMERLIST): %PUT ERROR: &ctry is an invalid country code. SYMBOLGEN: Macro variable CTRY resolves to zz ERROR: zz is an invalid country code. MLOGIC(CUSTOMERLIST): %PUT ERROR: Valid country codes include &ctrylist.. SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA. MLOGIC(CUSTOMERLIST): Ending execution. 91 options nosymbolgen nomprint nomlogic; ... View more

Hi @Tom thanks a lot for the explanations in your thread, I'll take more time to practice those techniques. I have tried the usage of minoperator together with mindelimiter for a &list of values separated by commas. The code and results are as follows, this is also a handy and simple usage I think. %macro customerlist(ctry)/minoperator mindelimiter=','; proc sql noprint; select distinct country into :ctrylist separated by "," from mc1.customers; quit; %if %superq(ctry) in %superq(ctrylist) %then %do; title "customers from &ctry"; proc sgplot data=mc1.customers; vbar group; yaxis grid; where country="&ctry"; run; %end; %else %do; %put ERROR: &ctry is an invalid country code.; %put ERROR: Valid country codes include &ctrylist..; %end; %mend customerlist; options symbolgen mprint mlogic; %customerlist(AU); %customerlist(zz); options nosymbolgen nomprint nomlogic; 69 %macro customerlist(ctry)/minoperator mindelimiter=','; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by "," 73 from mc1.customers; 74 quit; 75 %if %superq(ctry) in %superq(ctrylist) %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 options symbolgen mprint mlogic; 89 %customerlist(AU); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value AU MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by "," from mc1.customers; MPRINT(CUSTOMERLIST): quit; MLOGIC(CUSTOMERLIST): %IF condition %superq(ctry) in %superq(ctrylist) is TRUE SYMBOLGEN: Macro variable CTRY resolves to AU MPRINT(CUSTOMERLIST): title "customers from AU"; MPRINT(CUSTOMERLIST): proc sgplot data=mc1.customers; MPRINT(CUSTOMERLIST): vbar group; MPRINT(CUSTOMERLIST): yaxis grid; SYMBOLGEN: Macro variable CTRY resolves to AU MPRINT(CUSTOMERLIST): where country="AU"; MPRINT(CUSTOMERLIST): run; NOTE: PROCEDURE SGPLOT used (Total process time): NOTE: There were 90 observations read from the data set MC1.CUSTOMERS. WHERE country='AU'; MLOGIC(CUSTOMERLIST): Ending execution. 90 %customerlist(zz); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value zz MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by "," from mc1.customers; MPRINT(CUSTOMERLIST): quit; MLOGIC(CUSTOMERLIST): %IF condition %superq(ctry) in %superq(ctrylist) is FALSE MLOGIC(CUSTOMERLIST): %PUT ERROR: &ctry is an invalid country code. SYMBOLGEN: Macro variable CTRY resolves to zz ERROR: zz is an invalid country code. MLOGIC(CUSTOMERLIST): %PUT ERROR: Valid country codes include &ctrylist.. SYMBOLGEN: Macro variable CTRYLIST resolves to AT,AU,BE,CA,CH,CI,DE,DK,EG,ES,FI,FR,GB,GR,HR,IE,IL,IN,IT,LT,LU,MZ,NL,NO,NZ,PL,PT,SE,SI,TR, US,YU,ZA ERROR: Valid country codes include AT,AU,BE,CA,CH,CI,DE,DK,EG,ES,FI,FR,GB,GR,HR,IE,IL,IN,IT,LT,LU,MZ,NL,NO,NZ,PL,PT,SE,SI,TR,US,YU,ZA. MLOGIC(CUSTOMERLIST): Ending execution. 91 options nosymbolgen nomprint nomlogic; ... View more

Hi @Astounding thanks very much for telling me the usage of %index😀, I tested it on the code and it works brightly😀, saves a lot of headache😀. The materials did not insist on using IN operator, it was to let leaners practice the usage of MINOPERATOR. But I'll just use %index(&valuelist,&parametervalue) from now on, it's handy and simple. The code I tested is as follows: %macro customerlist(ctry)/minoperator; proc sql noprint; select distinct country into :ctrylist separated by " " from mc1.customers; quit; %if %index(&ctrylist,&ctry) %then %do; title "customers from &ctry"; proc sgplot data=mc1.customers; vbar group; yaxis grid; where country="&ctry"; run; %end; %else %do; %put ERROR: &ctry is an invalid country code.; %put ERROR: Valid country codes include &ctrylist..; %end; %mend customerlist; %customerlist(AU); options symbolgen mprint mlogic; %customerlist(zz); options nosymbolgen nomprint nomlogic; 71 %macro customerlist(ctry)/minoperator; 72 proc sql noprint; 73 select distinct country 74 into :ctrylist separated by " " 75 from mc1.customers; 76 quit; 77 %if %index(&ctrylist,&ctry) %then %do; 78 title "customers from &ctry"; 79 proc sgplot data=mc1.customers; 80 vbar group; 81 yaxis grid; 82 where country="&ctry"; 83 run; 84 %end; 85 %else %do; 86 %put ERROR: &ctry is an invalid country code.; 87 %put ERROR: Valid country codes include &ctrylist..; 88 %end; 89 %mend customerlist; 90 %customerlist(AU); 91 options symbolgen mprint mlogic; 92 %customerlist(zz); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value zz MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by " " from mc1.customers; MPRINT(CUSTOMERLIST): quit; SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA SYMBOLGEN: Macro variable CTRY resolves to zz MLOGIC(CUSTOMERLIST): %IF condition %index(&ctrylist,&ctry) is FALSE MLOGIC(CUSTOMERLIST): %PUT ERROR: &ctry is an invalid country code. SYMBOLGEN: Macro variable CTRY resolves to zz ERROR: zz is an invalid country code. MLOGIC(CUSTOMERLIST): %PUT ERROR: Valid country codes include &ctrylist.. SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA. MLOGIC(CUSTOMERLIST): Ending execution. 93 options nosymbolgen nomprint nomlogic; ... View more

Hi @Tom although I cannot access github, thanks a lot for telling me this!   So the pre-debugged macro you mentioned was to use the &parmerr combined with the %parmv (which has two parameters, one is the parameter of the outside macro such as ctry1, the other is the list of values such as _val=AT AU CA CH), if the condition of &parmerr is matched then put error message, if not then execute the code to produce what I want. Does this pre-debugged macro need to be downloaded from github? Does SAS has any built-in macro or options (I guess not‌‌😀) I can use for macro debugging (aside from the handy and simple options such as mlogic, mprint, and symbolgen) or options like validate in proc sql (in fact I personally do not think using validate helps much because when there is syntax error the log will give me error message even if I do not use validate, but at least when my syntax is ok it helps confirm that I do not have syntax error. On the other hand, in fact, when my code does not run through, I am more inclined to inspect all the details of the code by myself, as using extra tools sometimes make things more complex): 69 proc sql number; 70 validate 71 select make,origin,msrp 72 from sashelp.cars 73 where msrp<(select mean(msrp)+2*std(msrp) 74 from sashelp.cars); NOTE: PROC SQL statement has valid syntax. 75 quit; 76 proc sql number; 77 validate 78 select make,origin,msrp 79 from sashelp.cars 80 where msrp<(select mean(msrp)+2*std(msrp) 81 from sashelp.cars; _ 79 ERROR 79-322: Expecting a ). NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 81 ) _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 82 quit; NOTE: The SAS System stopped processing this step because of errors. ... View more

Hi @svh thanks a lot for telling this! I have not learnt much about CALL EXECUTE yet, but from your comments I can see that when I am thinking write a macro to solve the problem, maybe I can consider use some CALL EXECUTE first. I will learn to use CALL EXECUTE more later on! ... View more

Hi @ballardw thanks very much for your comments, it's a very good question which makes me explore and learn more.   Part of the purpose of the macro in the practice question I mentioned on page 285, Macro Essential1: course note pdf, is like this: 1) using proc sql to generate a country code &list from a dataset, and 2) if the macro parameter value (i.e., the country code) is among the &list then produce a plot, otherwise put an error message. The first time I did that question I made several mistakes and the macro did not work. Then I simplified the code by create a macro through %let list=; instead of using proc sql and made it worked (as you can see from this post). However, today, I run that part of code using the &list produced through proc sql, it did not work again, the code I tried is as follows: %macro customerlist(ctry)/minoperator; proc sql noprint; select distinct country into :ctrylist separated by " " from mc1.customers; quit; %if &ctry in &ctrylist %then %do; title "customers from &ctry"; proc sgplot data=mc1.customers; vbar group; yaxis grid; where country="&ctry"; run; %end; %else %do; %put ERROR: &ctry is an invalid country code.; %put ERROR: Valid country codes include &ctrylist..; %end; %mend customerlist; %customerlist(AU); %customerlist(zz); The %customerlist(AU) worked well, which produced a plot, but the %customerlist(zz) did not work and there was no error message in the log: 69 %macro customerlist(ctry)/minoperator; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by " " 73 from mc1.customers; 74 quit; 75 %if &ctry in &ctrylist %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 %customerlist(AU); NOTE: PROCEDURE SGPLOT used (Total process time): NOTE: There were 90 observations read from the data set MC1.CUSTOMERS. WHERE country='AU'; 89 %customerlist(zz); NOTE: PROCEDURE SQL used (Total process time): NOTE: PROCEDURE SGPLOT used (Total process time): I did not know why the &list created through %let list=; statement worked but the &list created through proc sql did not work. And in fact another technique that practice question to introduce is the usage of %superq(). I omitted that part because I thought it was too advanced for me and will leave it later. Then I think the reason the macro did not work was I did not use %superq(). After I added it the macro worked, the code and log is as follows: %macro customerlist(ctry)/minoperator; proc sql noprint; select distinct country into :ctrylist separated by " " from mc1.customers; quit; %if %superq(ctry) in %superq(ctrylist) %then %do; title "customers from &ctry"; proc sgplot data=mc1.customers; vbar group; yaxis grid; where country="&ctry"; run; %end; %else %do; %put ERROR: &ctry is an invalid country code.; %put ERROR: Valid country codes include &ctrylist..; %end; %mend customerlist; %customerlist(AU); %customerlist(zz); 69 %macro customerlist(ctry)/minoperator; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by " " 73 from mc1.customers; 74 quit; 75 %if %superq(ctry) in %superq(ctrylist) %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 %customerlist(AU); NOTE: PROCEDURE SQL used (Total process time): 69 %macro customerlist(ctry)/minoperator; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by " " 73 from mc1.customers; 74 quit; 75 %if %superq(ctry) in %superq(ctrylist) %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 %customerlist(AU); NOTE: PROCEDURE SQL used (Total process time): NOTE: PROCEDURE SGPLOT used (Total process time): NOTE: There were 90 observations read from the data set MC1.CUSTOMERS. WHERE country='AU'; 89 %customerlist(zz); ERROR: zz is an invalid country code. ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA.   It's a very brilliant point you said that "the macro processor generates code" and before writing a macro one should know what the code generated look like. I compared the log using macro debug options for the %let list=; macro and the proc sql &list macro, the logs are as follows. Without %superq(), SAS executed the proc sgplot step, whereas with the %superq(), SAS excuted the %put ERROR message step (see the part marked in red). 69 %macro customerlist(ctry)/minoperator; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by " " 73 from mc1.customers; 74 quit; 75 %if &ctry in &ctrylist %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 /*%customerlist(AU);*/ 89 options symbolgen mprint mlogic; 90 %customerlist(zz); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value zz MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by " " from mc1.customers; MPRINT(CUSTOMERLIST): quit; SYMBOLGEN: Macro variable CTRY resolves to zz SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA MLOGIC(CUSTOMERLIST): %IF condition &ctry in &ctrylist is TRUE SYMBOLGEN: Macro variable CTRY resolves to zz MPRINT(CUSTOMERLIST): title "customers from zz"; MPRINT(CUSTOMERLIST): proc sgplot data=mc1.customers; MPRINT(CUSTOMERLIST): vbar group; MPRINT(CUSTOMERLIST): yaxis grid; SYMBOLGEN: Macro variable CTRY resolves to zz MPRINT(CUSTOMERLIST): where country="zz"; MPRINT(CUSTOMERLIST): run; MLOGIC(CUSTOMERLIST): Ending execution. 91 options nosymbolgen nomprint nomlogic; 69 %macro customerlist(ctry)/minoperator; 70 proc sql noprint; 71 select distinct country 72 into :ctrylist separated by " " 73 from mc1.customers; 74 quit; 75 %if %superq(ctry) in %superq(ctrylist) %then %do; 76 title "customers from &ctry"; 77 proc sgplot data=mc1.customers; 78 vbar group; 79 yaxis grid; 80 where country="&ctry"; 81 run; 82 %end; 83 %else %do; 84 %put ERROR: &ctry is an invalid country code.; 85 %put ERROR: Valid country codes include &ctrylist..; 86 %end; 87 %mend customerlist; 88 /*%customerlist(AU);*/ 89 options symbolgen mprint mlogic; 90 %customerlist(zz); MLOGIC(CUSTOMERLIST): Beginning execution. MLOGIC(CUSTOMERLIST): Parameter CTRY has value zz MPRINT(CUSTOMERLIST): proc sql noprint; MPRINT(CUSTOMERLIST): select distinct country into :ctrylist separated by " " from mc1.customers; MPRINT(CUSTOMERLIST): quit; MLOGIC(CUSTOMERLIST): %IF condition %superq(ctry) in %superq(ctrylist) is FALSE MLOGIC(CUSTOMERLIST): %PUT ERROR: &ctry is an invalid country code. SYMBOLGEN: Macro variable CTRY resolves to zz ERROR: zz is an invalid country code. MLOGIC(CUSTOMERLIST): %PUT ERROR: Valid country codes include &ctrylist.. SYMBOLGEN: Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA. MLOGIC(CUSTOMERLIST): Ending execution. 91 options nosymbolgen nomprint nomlogic;   I agree with you that many of the "loop" type questions about macro can be solved with by group processing, but here according to my understanding so far, I do not think the requirements can be processed through by group, in that the error message cannot be generated through by group processing.   Thanks very much @ballardw again for your valuable input, which enables me learnt more! ... View more