LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2002, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Fri, 23 Aug 2002 09:51:08 -0400
Reply-To:   Philip Whittall <philip.whittall@UNILEVER.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Philip Whittall <philip.whittall@UNILEVER.COM>
Subject:   Re: Macro and SQL

Hi Christian,

Run with option MPRINT and SPOOL as well as SYMBOLGEN

which is really helpful for cracking these problems.

One thing I spotted is that

> %Let elem1 = 'WORK';

will mean that the macro elem1 will contain the quotes.

I suggest

> %Let elem1 = WORK;

and

> WHERE LIBNAME EQ "&elem1"

though this may not be all the things that you have to change.

Also beware when using where clauses on dictionary tables that names of memebers and variables tend to be uppercased so where clauses in the style

WHERE UPCASE(X)=UPCASE(Y) are generally safer.

Philip

On Thu, 22 Aug 2002 09:28:36 -0700, christian <vercellc@ENG.IT> 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


Back to: Top of message | Previous page | Main SAS-L page