As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Difference between revisions of "Making Lists"

From sasCommunity
Jump to: navigation, search
m (Proc SQL delete previous)
m (Make List of Files: fix link)
 
(20 intermediate revisions by 4 users not shown)
Line 1: Line 1:
Making Lists for List Processing  
+
Making Lists for List Processing
 +
 
 +
Author: [[Ronald_J._Fehd]]
 +
 
 +
keywords: control data set
  
 
== autoexec.sas ==
 
== autoexec.sas ==
These examples use a fileref '''SiteIncl''' which is allocated in an autoexec.
 
  
<pre>
+
These examples use a fileref '''site_inc''' which is allocated in an autoexec.
filename SiteIncl '.';
+
 
 +
<source lang="sas">
 +
filename site_inc '.';
 
*or;
 
*or;
*filename SiteIncl 'C:\SAS-site\includes';
+
*filename site_inc 'C:\SAS-site\includes';
</pre>
+
</source>
 
+
  
 
== Review SQL Dictionaries ==
 
== Review SQL Dictionaries ==
  
<pre>
+
<source lang="sas">
PROC SQL;describe table Dictionary.Dictionaries;
+
PROC SQL;describe table dictionary.dictionaries;
         create table List as
+
         create table list as
         select distinct Memname
+
         select distinct memname
         from Dictionary.Dictionaries;
+
         from dictionary.dictionaries;
  
         select catt('describe table Dictionary.', MemName)
+
         select catt('describe table dictionary.', memname)
into :List separated by ';'
+
      into :list separated by ';'
from List;
+
      from list;
 
+
      &list;
&List.;
+
 
         quit;
 
         quit;
</pre>
+
</source>
 +
 
  
 
== Make List of Files ==
 
== Make List of Files ==
  
<pre>
+
<source lang="sas">
* name: MlFiles-caller;
+
* name: ml-files-caller;
%Let MlFolder = C:\temp;
+
%let mlfolder = c:\temp;
%Let MlOut   = Work.FileNames;
+
%let mlout   = work.filenames;
%Include SiteIncl(MlFiles);
+
%include site_inc(ml-files);
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name      : MlFiles;
+
* name      : ml-files;
 
* description: read folder, return list of filenames;
 
* description: read folder, return list of filenames;
 
* purpose    : list processing;
 
* purpose    : list processing;
 
* parameter  : ;
 
* parameter  : ;
%*Let MlFolder = C:\Programs\SAS-9-2\SASFoundation\9.2\stat\sample;
+
%*let mlfolder = c:\programs\sas-9-2\sasfoundation\9.2\stat\sample;
%*Let MlOut   = Work.FileNames;
+
%*let mlout   = work.filenames;
%*Include SiteIncl(MlFiles);
+
%*include site_inc(ml-files);
  
data   &MlOut.(keep = FileName);
+
data &mlout.(keep = filename);
attrib FileName length = $72;
+
    attrib filename length = $72;
rc        = filename("mydir","&MlFolder.");
+
rc        = filename("mydir","&mlfolder.");
 
did      = dopen  ("mydir");
 
did      = dopen  ("mydir");
 
dim_files = dnum(did);
 
dim_files = dnum(did);
Line 57: Line 61:
 
rc = dclose(did);
 
rc = dclose(did);
 
run;
 
run;
</pre>
+
</source>
  
see also macro DIRLISTWIN: http://support.sas.com/kb/24/addl/fusion24820_1_dirlistwin.sas.txt
+
 
 +
see also
 +
* [http://support.sas.com/kb/24/addl/fusion24820_1_dirlistwin.sas.txt DIRLISTWIN macro]
 +
* [[GetFileInfo]]
 +
* [http://analytics.ncsu.edu/sesug/2007/AD09.pdf| Multiple File Processing with SAS®] Kevin McGowan
 +
 
 +
contributed by Nat Wooding to SAS-L on 2010-11-04;
 +
 
 +
Subject: RE: Can SAS pull info about MS Window folders?;
 +
 
 +
<source lang="sas">
 +
** this uses a pipe to gather the names of files in a folder
 +
and reads a single line from each file.
 +
note the quotes in the filename statement;
 +
 
 +
filename ft33f001 pipe  'attrib "c:\*.txt" '  ;
 +
 
 +
data _null_;
 +
  infile ft33f001 truncover;
 +
  input @12 csvfile $256.;
 +
  infile dummy filevar=csvfile firstobs=122 length=l  ;
 +
 
 +
  input line $varying256. l  ;
 +
  putlog 'NOTE: ' line=;
 +
run;
 +
 
 +
*the following is an attempt at reading directory info;
 +
FileName MyDir Pipe  'dir "C:\park"  ' ;
 +
 
 +
DATA all_dir;
 +
infile mydir lrecl=300 truncover ;
 +
input  @ ;
 +
file print;
 +
 
 +
if index( _infile_ , '/' ) ;* get lines with dates;
 +
if index( _infile_ , '<' ) then delete;* get rid of directories;
 +
 
 +
informat date mmddyy10. time time. ampm $2.  Size comma15. Name $25.;
 +
;
 +
input date  time  ampm    Size  Name &  ;
 +
 
 +
run;
 +
proc print;
 +
run;
 +
</source>
  
 
== Make List of Folders ==
 
== Make List of Folders ==
  
<pre>
+
<source lang="sas">
* name: MlFolders-caller;
+
* name: ml-folders-caller;
%Let MlFolder = C:\;
+
%let mlfolder = c:\;
%Let MlOut   = Work.Folders;
+
%let mlout   = work.folders;
%Include SiteIncl(MlFolders);
+
%include site_inc(ml-folders);
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
 
Note: Windows folder do not have a dot after FileName.
 
Note: Windows folder do not have a dot after FileName.
  
<pre>
+
<source lang="sas">
 
* name      : MlFolders;
 
* name      : MlFolders;
 
* description: make list of folders;
 
* description: make list of folders;
Line 79: Line 127:
 
* parameters ;
 
* parameters ;
 
%*Let MlFolder = C:\Program Files\;
 
%*Let MlFolder = C:\Program Files\;
%*Let MlOut    = Work.Folders;
+
%*Let MlOut    = work.folders;
%Include SiteIncl(MlFiles);
+
%include site_inc(ml-files);
  
DATA   &MlOut.(keep = Folder);
+
DATA &mlout(keep = folder);
attrib Folder length = $132;
+
    attrib folder length = $132;
do until(EndoFile);
+
do until(endofile);
   set &MlOut. (where = (not index(FileName,'.')))
+
   set &mlout (where = (not index(filename,'.')))
       end = EndoFile;
+
       end = endofile;
   Folder = catt("&MlFolder.\",FileName);
+
   folder = catt("&mlfolder.\",filename);
   if fileexist(Folder) then output;
+
   if fileexist(folder) then output;
 
   end;
 
   end;
 
stop;
 
stop;
 
run;
 
run;
</pre>
+
</source>
  
== Make List of Libnames ==
 
  
 +
=== filename pipe ===
 
<pre>
 
<pre>
PROC SQL;
+
Date:   Tue, 12 May 2015 17:28:51 -0400
   describe table Dictionary.Libnames;
+
To:      SAS-L
  create table Work.ListLibnames as
+
From:   Roger DeAngelis
      select *
+
Subject: Re: List of subfolders
      from Dictionary.Libnames
+
   ;
+
quit;
+
 
</pre>
 
</pre>
  
<pre>
+
<source lang="sas">
* name: MlLibname-caller;
+
filename pipetree pipe 'tree "C:\AYE835" /F /A' lrecl=3D5000;
%Let MllPrefix = SAShelp;
+
data _null_;
%Include SiteIncl(MlLibnames);
+
infile pipetree truncover;
Proc Print data = &SysLast.;
+
input dirlist $char1000.;
</pre>
+
if index(dirlist,'.') =0;
 +
put dirlist;
 +
run;
 +
</source>
  
<pre>
+
== Make List of Libnames ==
* name: MlLibnames;
+
 
PROC SQL;
+
<source lang="sas">
  create table Work.ListLibnames as
+
PROC SQL; describe table dictionary.libnames;
      select *
+
          create table work.listlibnames as
      from Dictionary.Libnames
+
          select *
      where Libname like "%upcase(&MllPrefix.%)"
+
          from dictionary.libnames;
 +
          quit;
 +
</source>
 +
 
 +
<source lang="sas">
 +
 
 +
* name: ml-libname-caller;
 +
%let mllprefix = sashelp;
 +
%include site_inc(ml-libnames);
 +
PROC print data = &syslast;
 +
</source>
 +
 
 +
<source lang="sas">
 +
* name: ml-libnames;
 +
PROC sql;
 +
    create table work.listlibnames as
 +
    select *
 +
    from dictionary.libnames
 +
    where libname like "%upcase(&mllprefix.%)"
 
   ;
 
   ;
 
quit;
 
quit;
</pre>
+
</source>
  
 
== Make List of Memnames ==
 
== Make List of Memnames ==
  
 
=== Proc Contents ===
 
=== Proc Contents ===
 +
The output of Proc Contents is always at a detail level of a library member.  The details are things like attributes of the variables or the properties of the indexes.
 +
 +
=== Proc Datasets ===
 +
 +
<source lang="sas">
 +
%let libref = work;
 +
%let out = work.library_members;
 +
 +
PROC sql; drop table &out;
 +
          quit;
 +
 +
ods listing select none;
 +
ods output members = &out
 +
          (label  = "members of &libref library");
 +
PROC datasets library = &libref;
 +
run;
 +
quit;
 +
ods output close;
 +
ods listing select all;
 +
 +
PROC print data=library_members;
 +
run;
 +
</source>
 +
  
 
=== Proc SQL ===
 
=== Proc SQL ===
  
<pre>
+
<source lang="sas">
* name: MlMemnames-caller;
+
* name: ml-memnames-caller;
%Let MlmLibname = Library;
+
%let mlmlibname = library;
%Let MlmLibname = SAShelp;
+
%let mlmlibname = sashelp;
%Include SiteIncl(MlMemnames);
+
%include site_inc(ml-memnames);
  
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name: MlMemnames;
+
* name: ml-memnames;
PROC SQL; create table Work.ListMemnames as
+
PROC sql; create table work.listmemnames as
 
           select *
 
           select *
           from  Dictionary.Tables
+
           from  dictionary.tables
           where  Libname eq "%upcase(&MlmLibname.)"
+
           where  libname eq "%upcase(&mlmlibname.)"
             and  MemType eq 'DATA';
+
             and  memtype eq 'DATA'
 +
          /* order by name | varnum /* ** */
 +
          ;
 
           quit;
 
           quit;
</pre>
+
</source>
  
 
== Make List of Names: Columns or Variables ==
 
== Make List of Names: Columns or Variables ==
 
 
=== Proc Contents ===
 
=== Proc Contents ===
  
<pre>
+
<source lang="sas">
* name: MlNames-caller;
+
* name: ml-names-caller;
%Let MlnLibname = Library;
+
%let mlnlibname = library;
%Let MlnLibname = SAShelp;
+
%let mlnlibname = sashelp;
%Let MlnMemname = _all_;
+
%let mlnmemname = _all_;
%Let MlnMemname = Class;
+
%let mlnmemname = class;
%Include SiteIncl(MlNames);
+
%include site_inc(ml-names);
Proc Print data = &SysLast.;
+
Proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name: MlNames;
+
* name: ml-names;
PROC Contents data  = &MlnLibname..&MlnMemnames.
+
PROC contents data  = &mlnlibname..&mlnmemnames.
 
                       noprint
 
                       noprint
               out    = Work.ListNames
+
               out    = work.listnames
             (keep  = Libname Memname Name Type Length Label VarNum
+
             (keep  = libname memname name type length label varnum
               where  = (MemType eq 'DATA') );
+
               where  = (memtype eq 'DATA') );
</pre>
+
 
 +
PROC sort data = &syslast;
 +
          *by    name  ;* alphabetical;
 +
          *by    varnum;* order in data set;
 +
</source>
  
 
=== Proc SQL ===
 
=== Proc SQL ===
  
<pre>
+
<source lang="sas">
* name: MlNames-caller;
+
* name: ml-names-caller;
%Let MlnLibname = Library;
+
%let mlnlibname = library;
%Let MlnLibname = SAShelp;
+
%let mlnlibname = sashelp;
%Let MlnMemname = Class;
+
%let mlnmemname = class;
%Include SiteIncl(MlNames);
+
%include site_inc(ml-names);
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name: MlNames;
+
* name: ml-names;
PROC SQL; create table Work.ListNames as
+
PROC sql; create table work.listnames as
 
           select *
 
           select *
           from  Dictionary.Columns
+
           from  dictionary.columns
           where  Libname eq "%upcase(&MlnLibname.)"
+
           where  libname eq "%upcase(&mlnlibname.)"
             and  Memname eq "%upcase(&MlnMemname.)"
+
             and  memname eq "%upcase(&mlnmemname.)"
             and  MemType eq 'DATA';
+
             and  memtype eq 'data';
 
           quit;
 
           quit;
</pre>
+
</source>
 +
 
 +
For usage see [[Processing_Variables]].
 +
 
 +
=== MWSUG.2016.TT03: ml-namex.sas: Make List Names eXtended ===
 +
 
 +
previous:
 +
Proc Freq.nlevels, SCL functions, Cardinality Ratio (CR) and CR-type
 +
 
 +
[http://www.mwsug.org/proceedings/2016/TT/MWSUG-2016-TT03.pdf MWSUG-2016, Tools of the Trade, paper TT03, Calculating Cardinality Ratio in Two Steps]
 +
 
 +
Make List-Names with Cardinality Ratio and CR-type
 +
see also [[Cardinality_Ratio]]
 +
 
 +
Note Style Guide enhancements:
 +
* %put
 +
** trace: <program-name> beginning and exit
 +
** info: _temporary macro variables created
 +
 
 +
<source lang="sas">
 +
%put trace: ml-namex make-list-names-cr beginning;
 +
%put echo parameters: &=libname &=memname;
 +
/*    name: ...\SAS-site\includes\ml-namex.sas
 +
    author: Ronald J. Fehd 2016
 +
------------------------------------------------------
 +
Summary:  description: make list of variable names,
 +
                      cardinality ratio and card-type
 +
              purpose: for list processing routines
 +
          --------------------------------------------
 +
Contexts: program group: procedure returns data set
 +
          program  type: subroutine
 +
          SAS      type: parameterized include
 +
          uses routines: n/a
 +
------------------------------------------------------
 +
Specifications: input  : macro variables
 +
                        libname: libref
 +
                        memname: data set name
 +
                process: proc freq n-levels
 +
                        copy memname.nobs, .nvars
 +
                              from local to global
 +
                        data: read var information
 +
                              with scl functions
 +
                        calculate cardinality ratio
 +
                              and card-ratio-type
 +
                output : list_names, a dimension table
 +
                        macro-variables: _length_few
 +
                        _list_few    _list_many_c
 +
                        _list_unique _list_many_n
 +
                        _max_length_c
 +
                          for data structure attrib
 +
                          valu_c length=$&_max_length_c
 +
note: output is ordered by varnum
 +
--------------------------------------------------------
 +
usage:  autoexec:
 +
filename site_inc '<...\SAS-site\includes>';
 +
- - -  ml-names-x-test.sas - - -
 +
%let libname = sashelp;
 +
%let memname = class;
 +
%include site_inc(ml-namex);
 +
proc sql; describe table list_names;
 +
          quit;
 +
proc print data = list_names;
 +
---------------------------------------------------------
 +
this is a Derivative Work of the SmryEachVar suite
 +
www.sascommunity.org/wiki/SmryEachVar_A_Data_Review_Suite
 +
this program is available on:
 +
http://www.sascommunity.org/wiki/Making_Lists
 +
http://www.mwsug.org/2016-proceedings.html paper TT03
 +
it is designed to work with parameterized includes:
 +
CxInclude, and SmryHuge; macros: CallMacro and CallText
 +
-------------------------------------------------------
 +
date-time: 2016-08-24 4:00:50 PM
 +
word count        words:  734
 +
                  lines:  195
 +
characters(no  spaces): 5410
 +
characters(with spaces): 7893
 +
**** ..................... */
 +
**** make dimension table: name + n-levels;
 +
ODS  exclude all; * noprint;
 +
PROC freq data  = &libname..&memname nlevels;
 +
    ods  output
 +
          nlevels= list_names
 +
        (keep  = tablevar          nlevels
 +
          rename =(tablevar= name    nlevels=n_levels));
 +
run;
 +
ODS select all;
 +
 
 +
****  copy      n-obs, n-vars from local to global;
 +
%let _dsid  = %sysfunc(open (&libname..&memname,i));
 +
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
 +
%let _n_vars = %sysfunc(attrn(&_dsid,nvar));
 +
%let _rc    = %sysfunc(close(&_dsid));
 +
%symdel _dsid _rc;
 +
 
 +
**** enhance dimension table with variable information,
 +
    cardinality ratio, card-ratio-type: cr-type;
 +
DATA &syslast(label=
 +
    "memname=&memname,obs=&_n_obs,vars=&_n_vars");
 +
    attrib memname    length = $32
 +
        %* primary key;
 +
            varnum      length =  8 label = 'var num'
 +
        %* cardinality information;
 +
            cr_type    length = $  %length(n-levels=1)
 +
                        label  =    'card. ratio type'
 +
            card_ratio  length =  8    %*range=(0:1];
 +
                        format =    bestd7.5
 +
                        label  =    'card. ratio'
 +
            n_levels    length =  8
 +
                        label  =    "n-levels nobs=&_n_obs"
 +
        %* variable information;
 +
            name        length = $32 label = 'name'
 +
            type        length = $ 1 %*range:(c,n) from scl;
 +
            length      length =  8
 +
            format      length = $49
 +
          informat      length = $49
 +
            label      length =$256
 +
          _length_few  length =  4
 +
    %*** _temp vars for macro variables of lists;
 +
    %*** 33: length(var-name)=32 +1 for delimiter;
 +
    %let _max_length_list = %sysfunc(min(
 +
                        %eval(33*&_n_vars),32767));
 +
          _list_few    length = $&_max_length_list
 +
          _list_many_c length = $&_max_length_list
 +
          _list_many_n length = $&_max_length_list
 +
          _list_unique length = $&_max_length_list;
 +
          %put echo calculation: &=_max_length_list;
 +
    %symdel _max_length_list;
 +
    array  _cr(&_n_vars);
 +
    *****  _lfs: list-few-sorted value=000:name;
 +
    array  _lfs(&_n_vars) $%eval(%length(&_n_obs)+33);
 +
    drop  _:;            * _temporary variables;
 +
    retain memname        "&memname"
 +
            _length_few 0  _max_length 1
 +
            _testing      %eval(
 +
            %sysfunc(getoption(mprint )) eq MPRINT
 +
        and %sysfunc(getoption(source2)) eq SOURCE2);
 +
 
 +
** loop: for each row, calculate cardinality ratio;
 +
do _i = 1 to dim(_cr);
 +
  set &syslast  (keep = n_levels)  point = _i;
 +
  _cr(_i) = n_levels / &_n_obs;
 +
  end;
 +
 
 +
***** calculate mean for select card_ratio to cr_type;
 +
_mean_cr = mean(of _cr(*));
 +
 
 +
*****  loop: read syslast(name n_levels), fetch var info;
 +
_dsid = open("&libname..&memname");** for scl functions;
 +
do _i = 1 to dim(_cr);
 +
  set &syslast  point = _i; *** primary-key=name;
 +
  if _testing then putlog name= n_levels=;
 +
  varnum    =        varnum  (_dsid,name  );
 +
  type      = lowcase(vartype  (_dsid,varnum));
 +
  length    =        varlength(_dsid,varnum);
 +
  format    =        varfmt  (_dsid,varnum);
 +
  informat  =        varinfmt (_dsid,varnum);
 +
  label      =        varlabel (_dsid,varnum);
 +
  card_ratio = _cr(_i);
 +
  select;
 +
    when(n_levels  eq 1) cr_type = 'n-levels=1';
 +
    when(card_ratio eq 1) cr_type = '.unique';%*row-id;
 +
    when(card_ratio gt
 +
                _mean_cr) cr_type = 'many'; %*analysis;
 +
    otherwise            cr_type = 'few';  %*by|class;
 +
    end;
 +
  ** make list_many_c, _many_n, for mvars;
 +
  if      cr_type eq 'many' then do;
 +
      if      type eq 'c' then
 +
            _list_many_c = catx(' ',_list_many_c,name);
 +
      else if type eq 'n' then
 +
            _list_many_n = catx(' ',_list_many_n,name);
 +
      end;
 +
  else if cr_type eq 'few'  then do;
 +
      **** save for sort: lfs(i)= '000:name';
 +
      _lfs(_i) = catx(':',put(n_levels
 +
                            ,z%length(&_n_obs).),name);
 +
      *** add one for delimiter = space;
 +
      _length_few = sum(1,_length_few);
 +
      if type eq 'c' then do;
 +
          _max_length=max(_max_length,length);
 +
          _length_few=sum(_length_few,length);
 +
          end;
 +
      else _length_few=sum(_length_few,%length(&_n_obs));
 +
      end;
 +
  else if cr_type eq '.unique'  then
 +
      _list_unique = catx(' ',_list_unique,name);
 +
  output;
 +
  end;
 +
_rc = close(_dsid);
 +
 
 +
**** loop: make list-few ordered by n-levels;
 +
call sortc(of _lfs(*));
 +
do _i = 1 to dim(_lfs);
 +
  if _testing and _lfs(_i) ne ' ' then putlog _lfs(_i)=;
 +
  ****        _lfs(_i)=0000:name;
 +
  name = scan(_lfs(_i),-1,':');
 +
  _list_few = catx(' ',_list_few,name);
 +
  end;
 +
 
 +
call symputx('_length_few'  ,_length_few );
 +
call symputx('_list_few'    ,_list_few  );
 +
call symputx('_list_many_c' ,_list_many_c);
 +
call symputx('_list_many_n' ,_list_many_n);
 +
call symputx('_list_unique' ,_list_unique);
 +
call symputx('_max_length_c',_max_length );
 +
stop;
 +
run;
 +
%put info: &=memname &=_n_obs &=_n_vars;
 +
%put info: &=_length_few;
 +
%put info: &=_list_few;
 +
%put info: &=_list_many_c;
 +
%put info: &=_list_many_n;
 +
%put info: &=_list_unique;
 +
%put info: &=_max_length_c;
 +
%put trace: ml-namex make-list-names-cr ending;
 +
</source>
  
 
== Auto Import all Excel Worksheets ==
 
== Auto Import all Excel Worksheets ==
Line 216: Line 526:
 
* Reply-To:    "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
 
* Reply-To:    "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
 
* Sender:      "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
 
* Sender:      "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
* Subject:      How to get existing Excel worksheet names into SAS  
+
* Subject:      How to get existing Excel worksheet names into SAS
  
 
Untested
 
Untested
  
<pre>
+
<source lang="sas">
 
filename topics dde 'excel|system!topics' lrecl=5000;
 
filename topics dde 'excel|system!topics' lrecl=5000;
  
Line 229: Line 539:
 
put topics;
 
put topics;
 
run;
 
run;
</pre>
+
</source>
  
 
provided by Ed Heaton
 
provided by Ed Heaton
  
<pre>
+
<source lang="sas">
LibName xlsLib "&workbook" access=readOnly ;
+
libname xlslib "&workbook" access=readonly ;
PROC SQL; Create table _data_ as
+
proc sql; create table _data_ as
           select MemName
+
           select memname
           from dictionary.Tables
+
           from dictionary.tables
           where (    ( LibName eq "XLSLIB" )
+
           where (    ( libname eq "XLSLIB" )
                   /* In case you don't want named ranges,
+
                   /* in case you don't want named ranges,
 
                   code the following. */
 
                   code the following. */
                 and (MemName contains '$' )
+
                 and (memname contains '$' )
                 and (scan( dequote(MemName) , 2 , '$' ) is missing )
+
                 and (scan( dequote(memname) , 2 , '$' ) is missing )
 
                 );
 
                 );
 
           quit;
 
           quit;
%let worksheets = &sysLast. ;
+
%let worksheets = &syslast ;
LibName xlsLib clear ;
+
libname xlslib clear ;
</pre>
+
</source>
  
 
provided by John Gerstle
 
provided by John Gerstle
  
<pre>
+
<source lang="sas">
libname test excel "&datapath.\Test..xls"  
+
libname test excel "&datapath.\Test..xls"
             ver    = 2002  
+
             ver    = 2002
             header = yes  
+
             header = yes
 
             mixed  = yes;
 
             mixed  = yes;
  
proc sql;select count(distinct memname), memname  
+
proc sql;select count(distinct memname), memname
into :no_census_datasets
+
        into   :no_census_datasets
             , :census_datasets separated by ' '  
+
             ,   :census_datasets separated by ' '
from dictionary.members  
+
        from   dictionary.members
where     memtype='DATA'  
+
        where memtype='DATA'
              and libname="TEST";
+
          and libname="TEST";
 
quit;
 
quit;
 
run;
 
run;
%put &no_census_datasets;  
+
%put &no_census_datasets;
 
%put &census_datasets;
 
%put &census_datasets;
  
Libname test close;
+
libname test close;
</pre>
+
</source>
  
 
+
<source lang="sas">
<pre>
+
* name: ml-sheets;
* name: MlSheets;
+
%*let folder =;
%*let Folder =;
+
%*let filename =;
%*let FileName =;
+
%*let mlsout = ;
%*let MlsOut = ;
+
data   work.sheetnames;
DATA   Work.SheetNames;
+
attrib folder   length = $132
attrib Folder   length = $132
+
       filename length = $ 72
       Filename length = $ 72
+
       sheetname length = $132;
       Sheetname length = $132;
+
 
** automagic happens here;
 
** automagic happens here;
 
* do ...;
 
* do ...;
Line 287: Line 596:
 
* stop;
 
* stop;
  
PROC Append base = &MlsOut.
+
proc append base = &mlsout.
             data = Work.SheetNames;
+
             data = work.sheetnames;
 
run;
 
run;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name: MlSheets-caller;
+
* name: ml-sheets-caller;
%Let MlfFolder = ..\xls;
+
%let mlffolder = ..\xls;
%Let MlfOut     = Work.ListXls;
+
%let mlfout     = work.listxls;
%Include SiteIncl(MlFiles);
+
%include site_inc(ml-files);
  
%Let MlxFolder = ..\xls;
+
%let mlxfolder = ..\xls;
%Let MlxOut     = Work.ListSheets;
+
%let mlxout     = work.listsheets;
%Include SiteIncl(MlSheets);
+
%include site_inc(ml-sheets);
  
%Let CxData   = &MlxOut.;
+
%let cx_data   = &mlxout;
%Let CxInclude = SiteIncl(MlSheets);* returns Work.ListSheets
+
%let cx_include = site_inc(mlsheets);* returns work.listsheets
%Include SiteIncl(MlFiles);
+
%include site_inc(ml-files);
  
 +
%let cx_data    = &mlfout;
 +
%let cx_include = site_inc(importxls);
 +
%include site_inc(ml-files);
  
%Let CxData   = &MlfOut.;
+
%let mlslibname = sashelp;
%Let CxInclude = SiteIncl(ImportXls);
+
%let mlsmemname = class;
%Include SiteIncl(MlFiles);
+
%let mlsname   = sex;
 +
%let mlsout    = work.listsheets;
 +
%include site_inc(ml-sheets);
 +
proc print data = &syslast;
 +
</source>
  
%Let MlsLibname = SAShelp;
 
%Let MlsMemname = Class;
 
%Let MlsName    = Sex;
 
%Let MlsOut    = Work.ListSheets;
 
%Include SiteIncl(MlSheets);
 
Proc Print data = &SysLast.;
 
</pre>
 
  
 
== Make List of Subsets ==
 
== Make List of Subsets ==
Line 323: Line 632:
 
=== Proc Freq ===
 
=== Proc Freq ===
  
<pre>
+
<source lang="sas">
* name: MlsFreq-caller;
+
* name: mls-freq-caller;
%Let MlsLibname = Library;
+
%let mlslibname = library;
%Let MlsLibname = SAShelp;
+
%let mlslibname = sashelp;
%Let MlsMemname = Class;
+
%let mlsmemname = class;
%Let MlsName   = Sex;
+
%let mlsname   = sex;
%Let MlsOut     = Work.ListSexes;
+
%let mlsout     = work.listsexes;
%Include SiteIncl(MlsFreq);
+
%include site_inc(mls-freq);
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
 
* name: MlsFreq.sas;
 
* name: MlsFreq.sas;
Proc Freq data  = &MlsLibname..&MlsMemname.;
+
Proc Freq data  = &MlsLibname..&MlsMemname;
           tables  &MlsName.
+
           tables  &MlsName
 
                 / list missing noprint
 
                 / list missing noprint
             out = &MlsOut.;
+
             out = &MlsOut;
 
run;
 
run;
</pre>
+
</source>
 +
 
 +
=== Proc Index ===
 +
 
 +
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1210A&L=sas-l&P=R3656
 +
 
 +
As of the 2012-Oct-02 this is a spoof.
 +
Check the SASware.Ballot to see if it has been implemented.
 +
 
 +
<source lang="sas">
 +
PROC Index data  = library.Mydata
 +
          index = MyData_VarA
 +
          out  = Work.List_MyData_VarA;
 +
</source>
 +
 
  
 
=== Proc Sort ===
 
=== Proc Sort ===
  
<pre>
+
<source lang="sas">
* name: MlsSort-caller;
+
* name: mls-sort-caller;
%Let MlsLibname = Library;
+
%let mlslibname = library;
%Let MlsLibname = SAShelp;
+
%let mlslibname = sashelp;
%Let MlsMemname = Class;
+
%let mlsmemname = class;
%Let MlsNames   = Sex;
+
%let mlsnames   = sex;
%Let MlsOut     = Work.ListSexes;
+
%let mlsout     = work.listsexes;
%Include SiteIncl(MlsSort);
+
%include site_inc(mls-sort);
Proc Print data = &SysLast.;
+
proc print data = &syslast;
</pre>
+
</source>
  
<pre>
+
<source lang="sas">
* name: MlsSort.sas;
+
* name: mls-sort.sas;
Proc Sort data = &MlsLibname..&MlsMemname.
+
proc sort data = &mlslibname..&mlsmemname.
         (keep = &MlsNames.)
+
         (keep = &mlsnames)
           out  = &MlsOut.
+
           out  = &mlsout
 
                 nodupkey;
 
                 nodupkey;
           by    &MlsNames.;
+
           by    &mlsnames;
 
run;
 
run;
</pre>
+
</source>
  
 
=== Proc SQL ===
 
=== Proc SQL ===
  
<pre>
+
 
 +
<source lang="sas">
 
* name:;
 
* name:;
 
select distinct ...;
 
select distinct ...;
</pre>
+
</source>
  
 
=== Proc Summary ===
 
=== Proc Summary ===
  
<pre>
+
<source lang="sas">
 
* name:;
 
* name:;
</pre>
+
</source>
  
 
=== Proc Univariate ===
 
=== Proc Univariate ===
  
<pre>
+
<source lang="sas">
 
* name:;
 
* name:;
</pre>
+
</source>
  
 +
== References ==
  
== References ==
+
* [[Cardinality_Ratio]]
 
* [[Journeymens_Tools]]
 
* [[Journeymens_Tools]]
 +
* [[GetFileInfo]]
 
* [[List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables]]
 
* [[List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables]]
 +
* [[Processing_Data_Sets]]
 +
* [[Processing_Variables]]
 
* [[SmryEachVar_A_Data_Review_Suite]]
 
* [[SmryEachVar_A_Data_Review_Suite]]
 
* [[Writing_Testing_Aware_Programs]]
 
* [[Writing_Testing_Aware_Programs]]
 +
* [http://analytics.ncsu.edu/sesug/2007/AD09.pdf Multiple File Processing with SAS®], Kevin McGowan
 +
* [http://stackoverflow.com/questions/16078421/how-do-i-get-a-list-of-folders-and-subfolders-without-the-files how to get list of folders and subfolders]
 +
* [http://www.wuss.org/proceedings12/55.pdf Obtaining A List of Files In A Directory Using SAS® Functions], Jack Hamilton
  
--macro maven == the radical programmer 11:13, 18 August 2008 (EDT)
+
-- created by [[User:Rjf2]] 18 August 2008
 
+
* This page was last modified 14:34, 29 August 2008.
+
** This page has been accessed 502 times as of 2008-Sep-09.
+
* This page was last modified 20:32:26, 2008-10-17.
+
** This page has been accessed 3,504 times as of 2009-Jan-07.
+
  
 +
--[[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro.maven == the radical programmer]] 07:37, 24 June 2012 (EDT)
  
[[Category:List Processing]]
+
[[Category:List_Processing]]
 +
[[Category:List_Processing_by_Ron_Fehd]]
 
[[Category:Making_subsets]]
 
[[Category:Making_subsets]]
[[Category:Subroutine Library]]
+
[[Category:SQL_by_Ron_Fehd]]
 +
[[Category:Subroutine_Library]]
 +
[[Category:Theory_by_Ron_Fehd]]
 
[[Category:Utility]]
 
[[Category:Utility]]
 +
[[Category:Utilities_by_Ron_Fehd]]

Latest revision as of 10:08, 9 November 2017

Making Lists for List Processing

Author: Ronald_J._Fehd

keywords: control data set

autoexec.sas

These examples use a fileref site_inc which is allocated in an autoexec.

filename site_inc '.';
*or;
*filename site_inc 'C:\SAS-site\includes';

Review SQL Dictionaries

PROC SQL;describe table dictionary.dictionaries;
         create table list as
         select distinct memname
         from dictionary.dictionaries;
 
         select catt('describe table dictionary.', memname)
		       into :list separated by ';'
		       from list;
       	 &list;
         quit;


Make List of Files

* name: ml-files-caller;
%let mlfolder = c:\temp;
%let mlout    = work.filenames;
%include site_inc(ml-files);
proc print data = &syslast;
* name       : ml-files;
* description: read folder, return list of filenames;
* purpose    : list processing;
* parameter  : ;
%*let mlfolder = c:\programs\sas-9-2\sasfoundation\9.2\stat\sample;
%*let mlout    = work.filenames;
%*include site_inc(ml-files);
 
data &mlout.(keep = filename);
     attrib filename length = $72;
rc        = filename("mydir","&mlfolder.");
did       = dopen   ("mydir");
dim_files = dnum(did);
do i = 1 to dim_files;
   filename = dread(did,i);
   output;
   end;
rc = dclose(did);
run;


see also

contributed by Nat Wooding to SAS-L on 2010-11-04;

Subject: RE: Can SAS pull info about MS Window folders?;

** this uses a pipe to gather the names of files in a folder
and reads a single line from each file.
note the quotes in the filename statement;
 
filename ft33f001 pipe  'attrib "c:\*.txt" '  ;
 
data _null_;
   infile ft33f001 truncover;
   input @12 csvfile $256.;
   infile dummy filevar=csvfile firstobs=122 length=l   ;
 
   input line $varying256. l   ;
   putlog 'NOTE: ' line=;
run;
 
*the following is an attempt at reading directory info;
FileName MyDir Pipe  'dir "C:\park"  ' ;
 
DATA all_dir;
infile mydir lrecl=300 truncover ;
input  @ ;
file print;
 
if index( _infile_ , '/' ) ;* get lines with dates;
if index( _infile_ , '<' ) then delete;* get rid of directories;
 
informat date mmddyy10. time time. ampm $2.   Size comma15. Name $25.;
 ;
input date  time  ampm    Size  Name &  ;
 
run;
proc print;
run;

Make List of Folders

* name: ml-folders-caller;
%let mlfolder = c:\;
%let mlout    = work.folders;
%include site_inc(ml-folders);
proc print data = &syslast;

Note: Windows folder do not have a dot after FileName.

* name       : MlFolders;
* description: make list of folders;
* purpose    : list processing;
* parameters ;
%*Let MlFolder = C:\Program Files\;
%*Let MlOut    = work.folders;
%include site_inc(ml-files);
 
DATA &mlout(keep = folder);
     attrib folder length = $132;
do until(endofile);
   set &mlout (where = (not index(filename,'.')))
       end = endofile;
   folder = catt("&mlfolder.\",filename);
   if fileexist(folder) then output;
   end;
stop;
run;


filename pipe

Date:    Tue, 12 May 2015 17:28:51 -0400
To:      SAS-L
From:    Roger DeAngelis
Subject: Re: List of subfolders
filename pipetree pipe 'tree "C:\AYE835" /F /A' lrecl=3D5000;
data _null_;
infile pipetree truncover;
input dirlist $char1000.;
if index(dirlist,'.') =0;
put dirlist;
run;

Make List of Libnames

PROC SQL; describe table dictionary.libnames;
          create table work.listlibnames as
          select *
          from dictionary.libnames;
          quit;
* name: ml-libname-caller;
%let mllprefix = sashelp;
%include site_inc(ml-libnames);
PROC print data = &syslast;
* name: ml-libnames;
PROC sql;
     create table work.listlibnames as
     select *
     from dictionary.libnames
     where libname like "%upcase(&mllprefix.%)"
   ;
quit;

Make List of Memnames

Proc Contents

The output of Proc Contents is always at a detail level of a library member. The details are things like attributes of the variables or the properties of the indexes.

Proc Datasets

%let libref = work;
%let out = work.library_members;
 
PROC sql; drop table &out;
          quit;
 
ods listing select none;
ods output members = &out
          (label   = "members of &libref library");
PROC datasets library = &libref;
run;
quit;
ods output close;
ods listing select all;
 
PROC print data=library_members;
run;


Proc SQL

* name: ml-memnames-caller;
%let mlmlibname = library;
%let mlmlibname = sashelp;
%include site_inc(ml-memnames);
 
proc print data = &syslast;
* name: ml-memnames;
PROC sql; create table work.listmemnames as
          select *
          from   dictionary.tables
          where  libname eq "%upcase(&mlmlibname.)"
            and  memtype eq 'DATA'
          /* order by name | varnum /* ** */
          ;
          quit;

Make List of Names: Columns or Variables

Proc Contents

* name: ml-names-caller;
%let mlnlibname = library;
%let mlnlibname = sashelp;
%let mlnmemname = _all_;
%let mlnmemname = class;
%include site_inc(ml-names);
Proc print data = &syslast;
* name: ml-names;
PROC contents data   = &mlnlibname..&mlnmemnames.
                       noprint
              out    = work.listnames
             (keep   = libname memname name type length label varnum
              where  = (memtype eq 'DATA') );
 
PROC sort data = &syslast;
          *by     name  ;* alphabetical;
          *by     varnum;* order in data set;

Proc SQL

* name: ml-names-caller;
%let mlnlibname = library;
%let mlnlibname = sashelp;
%let mlnmemname = class;
%include site_inc(ml-names);
proc print data = &syslast;
* name: ml-names;
PROC sql; create table work.listnames as
          select *
          from   dictionary.columns
          where  libname eq "%upcase(&mlnlibname.)"
            and  memname eq "%upcase(&mlnmemname.)"
            and  memtype eq 'data';
          quit;

For usage see Processing_Variables.

MWSUG.2016.TT03: ml-namex.sas: Make List Names eXtended

previous: Proc Freq.nlevels, SCL functions, Cardinality Ratio (CR) and CR-type

MWSUG-2016, Tools of the Trade, paper TT03, Calculating Cardinality Ratio in Two Steps

Make List-Names with Cardinality Ratio and CR-type see also Cardinality_Ratio

Note Style Guide enhancements:

  •  %put
    • trace: <program-name> beginning and exit
    • info: _temporary macro variables created
%put trace: ml-namex make-list-names-cr beginning;
%put echo parameters: &=libname &=memname;
 /*    name: ...\SAS-site\includes\ml-namex.sas
     author: Ronald J. Fehd 2016
------------------------------------------------------
Summary:  description: make list of variable names,
                       cardinality ratio and card-type
              purpose: for list processing routines
          --------------------------------------------
Contexts: program group: procedure returns data set
          program  type: subroutine
          SAS      type: parameterized include
          uses routines: n/a
------------------------------------------------------
Specifications: input  : macro variables
                         libname: libref
                         memname: data set name
                process: proc freq n-levels
                         copy memname.nobs, .nvars
                               from local to global
                         data: read var information
                               with scl functions
                         calculate cardinality ratio
                               and card-ratio-type
                output : list_names, a dimension table
                         macro-variables: _length_few
                         _list_few    _list_many_c
                         _list_unique _list_many_n
                         _max_length_c
                           for data structure attrib
                           valu_c length=$&_max_length_c
note: output is ordered by varnum
--------------------------------------------------------
usage:   autoexec:
filename site_inc '<...\SAS-site\includes>';
- - -  ml-names-x-test.sas - - -
%let libname = sashelp;
%let memname = class;
%include site_inc(ml-namex);
proc sql; describe table list_names;
          quit;
proc print data = list_names;
---------------------------------------------------------
this is a Derivative Work of the SmryEachVar suite
www.sascommunity.org/wiki/SmryEachVar_A_Data_Review_Suite
this program is available on:
http://www.sascommunity.org/wiki/Making_Lists
http://www.mwsug.org/2016-proceedings.html paper TT03
it is designed to work with parameterized includes:
CxInclude, and SmryHuge; macros: CallMacro and CallText
-------------------------------------------------------
date-time: 2016-08-24 4:00:50 PM
word count        words:  734
                  lines:  195
characters(no   spaces): 5410
characters(with spaces): 7893
**** ..................... */
**** make dimension table: name + n-levels;
ODS  exclude all; * noprint;
PROC freq data   = &libname..&memname nlevels;
     ods  output
          nlevels= list_names
         (keep   = tablevar           nlevels
          rename =(tablevar= name     nlevels=n_levels));
run;
ODS select all;
 
****  copy      n-obs, n-vars from local to global;
%let _dsid   = %sysfunc(open (&libname..&memname,i));
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
%let _n_vars = %sysfunc(attrn(&_dsid,nvar));
%let _rc     = %sysfunc(close(&_dsid));
%symdel _dsid _rc;
 
**** enhance dimension table with variable information,
     cardinality ratio, card-ratio-type: cr-type;
DATA &syslast(label=
    "memname=&memname,obs=&_n_obs,vars=&_n_vars");
     attrib memname     length = $32
         %* primary key;
            varnum      length =   8 label = 'var num'
         %* cardinality information;
            cr_type     length = $  %length(n-levels=1)
                        label  =    'card. ratio type'
            card_ratio  length =   8     %*range=(0:1];
                        format =    bestd7.5
                        label  =    'card. ratio'
            n_levels    length =   8
                        label  =    "n-levels nobs=&_n_obs"
         %* variable information;
            name        length = $32 label = 'name'
            type        length = $ 1 %*range:(c,n) from scl;
            length      length =   8
            format      length = $49
          informat      length = $49
            label       length =$256
           _length_few  length =   4
     %*** _temp vars for macro variables of lists;
     %*** 33: length(var-name)=32 +1 for delimiter;
     %let _max_length_list = %sysfunc(min(
                        %eval(33*&_n_vars),32767));
           _list_few    length = $&_max_length_list
           _list_many_c length = $&_max_length_list
           _list_many_n length = $&_max_length_list
           _list_unique length = $&_max_length_list;
          %put echo calculation: &=_max_length_list;
     %symdel _max_length_list;
     array  _cr(&_n_vars);
     *****  _lfs: list-few-sorted value=000:name;
     array  _lfs(&_n_vars) $%eval(%length(&_n_obs)+33);
     drop   _:;            * _temporary variables;
     retain memname        "&memname"
            _length_few 0  _max_length 1
            _testing       %eval(
             %sysfunc(getoption(mprint )) eq MPRINT
         and %sysfunc(getoption(source2)) eq SOURCE2);
 
** loop: for each row, calculate cardinality ratio;
do _i = 1 to dim(_cr);
   set &syslast   (keep = n_levels)   point = _i;
   _cr(_i) = n_levels / &_n_obs;
   end;
 
***** calculate mean for select card_ratio to cr_type;
_mean_cr = mean(of _cr(*));
 
*****   loop: read syslast(name n_levels), fetch var info;
_dsid = open("&libname..&memname");** for scl functions;
do _i = 1 to dim(_cr);
   set &syslast   point = _i; *** primary-key=name;
   if _testing then putlog name= n_levels=;
   varnum     =         varnum   (_dsid,name  );
   type       = lowcase(vartype  (_dsid,varnum));
   length     =         varlength(_dsid,varnum);
   format     =         varfmt   (_dsid,varnum);
   informat   =         varinfmt (_dsid,varnum);
   label      =         varlabel (_dsid,varnum);
   card_ratio = _cr(_i);
   select;
     when(n_levels   eq 1) cr_type = 'n-levels=1';
     when(card_ratio eq 1) cr_type = '.unique';%*row-id;
     when(card_ratio gt
                 _mean_cr) cr_type = 'many'; %*analysis;
     otherwise             cr_type = 'few';  %*by|class;
     end;
   ** make list_many_c, _many_n, for mvars;
   if      cr_type eq 'many' then do;
      if      type eq 'c' then
            _list_many_c = catx(' ',_list_many_c,name);
      else if type eq 'n' then
            _list_many_n = catx(' ',_list_many_n,name);
      end;
   else if cr_type eq 'few'  then do;
      **** save for sort: lfs(i)= '000:name';
      _lfs(_i) = catx(':',put(n_levels
                             ,z%length(&_n_obs).),name);
      *** add one for delimiter = space;
      _length_few = sum(1,_length_few);
      if type eq 'c' then do;
           _max_length=max(_max_length,length);
           _length_few=sum(_length_few,length);
           end;
      else _length_few=sum(_length_few,%length(&_n_obs));
      end;
   else if cr_type eq '.unique'  then
       _list_unique = catx(' ',_list_unique,name);
   output;
   end;
_rc = close(_dsid);
 
**** loop: make list-few ordered by n-levels;
call sortc(of _lfs(*));
do _i = 1 to dim(_lfs);
   if _testing and _lfs(_i) ne ' ' then putlog _lfs(_i)=;
   ****        _lfs(_i)=0000:name;
   name = scan(_lfs(_i),-1,':');
   _list_few = catx(' ',_list_few,name);
   end;
 
call symputx('_length_few'  ,_length_few );
call symputx('_list_few'    ,_list_few   );
call symputx('_list_many_c' ,_list_many_c);
call symputx('_list_many_n' ,_list_many_n);
call symputx('_list_unique' ,_list_unique);
call symputx('_max_length_c',_max_length );
stop;
run;
%put info: &=memname &=_n_obs &=_n_vars;
%put info: &=_length_few;
%put info: &=_list_few;
%put info: &=_list_many_c;
%put info: &=_list_many_n;
%put info: &=_list_unique;
%put info: &=_max_length_c;
%put trace: ml-namex make-list-names-cr ending;

Auto Import all Excel Worksheets

The SAS Tech Report has a link to a usage note that lists new SAS tools that speed up the process of importing Excel workbooks or Access data bases. The title of the note is

Usage Note 13348: Using SAS(r) autoimporterv9 programs to automatically import all worksheets in a Microsoft Excel workbook And the url is

http://support.sas.com/kb/13/348.html

Make List of Excel SheetNames

Little Birdie opines:

I think all but one of the solutions put forth so far require SAS/ACCESS for PC Files, and the remaining one requires that SAS is running on Windows. If you don't meet these requirements, then you cannot use the techniques.

I just thought that I might save you some time, not having to try solutions that are not applicable to your environment.

http://listserv.uga.edu/cgi-bin/wa?A2=ind0107C&L=sas-l&P=R1794&D=0&H=0&O=D&T=1

  • Date: Mon, 16 Jul 2001 15:15:11 +0100
  • Reply-To: "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
  • Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
  • Subject: How to get existing Excel worksheet names into SAS

Untested

filename topics dde 'excel|system!topics' lrecl=5000;
 
data _null_;
length topics $1000;
infile topics pad dsd notab dlm='09'x;
input topics $ @@;
put topics;
run;

provided by Ed Heaton

libname xlslib "&workbook" access=readonly ;
proc sql; create table _data_ as
          select memname
          from dictionary.tables
          where (    ( libname eq "XLSLIB" )
                  /* in case you don't want named ranges,
                   code the following. */
                 and (memname contains '$' )
                 and (scan( dequote(memname) , 2 , '$' ) is missing )
                 );
          quit;
%let worksheets = &syslast ;
libname xlslib clear ;

provided by John Gerstle

libname test excel "&datapath.\Test..xls"
             ver    = 2002
             header = yes
             mixed  = yes;
 
proc sql;select count(distinct memname), memname
	        into   :no_census_datasets
            ,   :census_datasets separated by ' '
	        from   dictionary.members
	        where  memtype='DATA'
           and  libname="TEST";
quit;
run;
%put &no_census_datasets;
%put &census_datasets;
 
libname test close;
* name: ml-sheets;
%*let folder =;
%*let filename =;
%*let mlsout = ;
data   work.sheetnames;
attrib folder    length = $132
       filename  length = $ 72
       sheetname length = $132;
** automagic happens here;
* do ...;
*    output;
*    end;
* stop;
 
proc append base = &mlsout.
            data = work.sheetnames;
run;
* name: ml-sheets-caller;
%let mlffolder = ..\xls;
%let mlfout     = work.listxls;
%include site_inc(ml-files);
 
%let mlxfolder = ..\xls;
%let mlxout     = work.listsheets;
%include site_inc(ml-sheets);
 
%let cx_data    = &mlxout;
%let cx_include = site_inc(mlsheets);* returns work.listsheets
%include site_inc(ml-files);
 
%let cx_data    = &mlfout;
%let cx_include = site_inc(importxls);
%include site_inc(ml-files);
 
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsname    = sex;
%let mlsout     = work.listsheets;
%include site_inc(ml-sheets);
proc print data = &syslast;


Make List of Subsets

Proc Freq

* name: mls-freq-caller;
%let mlslibname = library;
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsname    = sex;
%let mlsout     = work.listsexes;
%include site_inc(mls-freq);
proc print data = &syslast;
* name: MlsFreq.sas;
Proc Freq data   = &MlsLibname..&MlsMemname;
          tables   &MlsName
                 / list missing noprint
             out = &MlsOut;
run;

Proc Index

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1210A&L=sas-l&P=R3656

As of the 2012-Oct-02 this is a spoof. Check the SASware.Ballot to see if it has been implemented.

PROC Index data  = library.Mydata
           index = MyData_VarA
           out   = Work.List_MyData_VarA;


Proc Sort

* name: mls-sort-caller;
%let mlslibname = library;
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsnames   = sex;
%let mlsout     = work.listsexes;
%include site_inc(mls-sort);
proc print data = &syslast;
* name: mls-sort.sas;
proc sort data = &mlslibname..&mlsmemname.
         (keep = &mlsnames)
          out  = &mlsout
                 nodupkey;
          by     &mlsnames;
run;

Proc SQL

* name:;
select distinct ...;

Proc Summary

* name:;

Proc Univariate

* name:;

References

-- created by User:Rjf2 18 August 2008

--Ronald_J._Fehd macro.maven == the radical programmer 07:37, 24 June 2012 (EDT)