|
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
| 
