[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: tip concise CONTENTS via SQL macro
From:       "Fehd, Ronald J. (PHPPO)" <rjf2 () CDC ! GOV>
Date:       2003-06-30 20:13:58
[Download RAW message or body]

As a result of thinking over just how much (virtual)
ink I use when doing
PROC CONTENTS data = WORK._ALL_;
I have reduced the output with my List Processing w/SQL ideas:
see macro below .sig

as always commentary invited and welcomed
note %local allocation with macro functions in parameter list

TGIHW: Thank Goodness it's a Holiday Week!

Ron Fehd  the List Processing w/SQL maven  CDC Atlanta GA USA RJF2@cdc.gov

Maximum fun! -- Linus Torvalds, creator of Linux

Minimum ink usage! -- RJF2, creator of SQL tools


 /*CONTENTS: return short contents via SQL
usage:
%CONTENTS(DATA = WORK._ALL_ nods);
%CONTENTS(DATA = WORK._ALL_ );
%CONTENTS(DATA = WORK.A );
*** ............................. ......... */
%MACRO CONTENTS
(DATA    = WORK._ALL_ NODS
           /*local*/
,LIBNAME = %scan(&DATA.,1,.)
,MEMNAME = %scan(&DATA.,2,%str(. ))
,NODS    = %scan(&DATA.,2,%str(  ))
,TESTING = 0
,TITLEN  = 2
)/ des = "SQL CONTENTS short form &SYSDATE. &SYSUSERID."
         /*SASMACRO.cat.description*/
;/* change notes -----------
RJF2 03Jun30 written
*** ........ */
%*upcase for use in conditions,
  UPCASE did not work in parm list;
%LET LIBNAME = %UPCASE(&LIBNAME.);
%LET MEMNAME = %UPCASE(&MEMNAME.);
%LET NODS    = %UPCASE(&NODS.   );
%IF &TESTING. %THEN %DO;
%PUT DATA    = <&DATA.>;
%PUT LIBNAME = <&LIBNAME.>;
%PUT MEMNAME = <&MEMNAME.>;
%PUT NODS    = <&NODS.>;
%*GOTO EXIT;%END;

TITLE&TITLEN. "CONTENTS: &LIBNAME..&MEMNAME. &NODS. via SQL";

%IF     "&MEMNAME." eq "_ALL_"
    and "&NODS."    eq "NODS" %THEN %DO;

PROC SQL; create table  ZQTABLES
          as     select LibName ,MemName ,Nobs ,Nvar ,MemLabel
          from          DICTIONARY.TABLES
          where         LibName eq "&LIBNAME."
          ;quit;

PROC PRINT data = ZQTABLES;
           by     LibName;
           id     LibName;

proc DATASETS library  = WORK
              memtype  = data
                         nolist;
              delete     ZQTABLES;
              quit;
%GOTO EXIT;%END;
%*............................ %IF MEMNAME EQ _ALL_ and NODS;%END;

PROC SQL; create table  ZQCOLUMNS
          as     select LibName ,MemName ,Name ,Type ,Length ,Label
          from          DICTIONARY.COLUMNS
          where         LibName eq "&LIBNAME."
          %IF           "&MEMNAME." ne "_ALL_" %THEN %DO;
                    and MemName eq "&MEMNAME."       %END;
          ;quit;

PROC PRINT data = ZQCOLUMNS;
           by     LibName MemName ;
           id     LibName MemName;

proc DATASETS library  = WORK
              memtype  = data
                         nolist;
              delete     ZQCOLUMNS;
              quit;
%EXIT: run;%* ....................... ......... *;%MEND;
 /* test section ----------------------------------- **
%MACRO MAKEDATA(DATA,N);DATA &DATA.(label="&DATA.");
do i = 1 to &N.;output;end;stop;%mend;
%MAKEDATA(A,1);
%MAKEDATA(B,2);
%MAKEDATA(C,3);

%CONTENTS(DATA = WORK._ALL_ nods);
%CONTENTS(DATA = WORK._ALL_ );
%CONTENTS(DATA = WORK.A );
 /*** ************************************************ */

[prev in list] [next in list] [prev in thread] [next in thread]