Christian, I don't see anywhere in the code where you are specifying an order of variables, such %let vars=var1 var3 var4 var5 var6 , where the data is then sorted by &vars (your new sort order). Secondly, the "INTO" creation of a macro variable requires the statement SELECT x into :macrovar ......;, the ampersand is incorrect in the select here. Ed.

christian wrote:Hi! I need to create a Macro for sorting a dataset using SQL instead 'Proc Sort'. Here's my wrong code:

%Macro engSort(ds_to_sort,ds_out);

%Let elem1 = %scan(%nrquote(&ds_to_sort),1,%nrquote(.)); %Let elem2 = %scan(%nrquote(&ds_to_sort),2,%nrquote(.));

%if &elem2 eq %nrquote() %then %do; %Let elem2 = &elem1; %Let elem1 = 'WORK'; %end;

proc sql noprint;

SELECT NAME INTO :&vars SEPARATED BY ',' FROM DICTIONARY.COLUMNS WHERE LIBNAME EQ '&elem1' AND MEMNAME EQ '&elem2';

create table &ds_out as select * from &ds_to_sort order by &vars;

quit;

%Mend;

/********************************************************/ THE EXECUTION RESULT:

29 options symbolgen; 30 %engSortA(oltpdata.Diff_persone_fisiche,prova); SYMBOLGEN: Macro variable DS_TO_SORT resolves to oltpdata.Diff_persone_fisiche SYMBOLGEN: Macro variable DS_TO_SORT resolves to oltpdata.Diff_persone_fisiche SYMBOLGEN: Macro variable ELEM2 resolves to Diff_persone_fisiche

NOTE: Line generated by the invoked macro "ENGSORT". 30 SELECT NAME INTO :&vars SEPARATED BY ',' - 22 ---- 202 30 ! FROM DICTIONARY.COLUMNS WHERE LIBNAME EQ '&elem1' AND MEMNAME 30 ! EQ '&elem2'; create table &ds_out as WARNING: Apparent symbolic reference VARS not resolved. ERROR 22-322: Expecting a name.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

SYMBOLGEN: Macro variable DS_OUT resolves to prova SYMBOLGEN: Macro variable DS_TO_SORT resolves to oltpdata.Diff_persone_fisiche NOTE 137-205: Line generated by the invoked macro "ENGSORTA". 30 select * from &ds_to_sort order by &vars;

-

22 30 ! quit; ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT, USER.

WARNING: Apparent symbolic reference VARS not resolved. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 0.01 seconds cpu time 0.01 seconds

Thanks to all for help!

Chris

--------------------------------- Do You Yahoo!? HotJobs, a Yahoo! service - Search Thousands of New Jobs