Making Lists

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

• DIRLISTWIN
• GetFileInfo
• 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?;

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

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

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;
 /*    name: ...\SAS-site\includes\ml-namex.sas
     author: Ronald J. Fehd 2015
----------------------------------------------------------
Summary: description: make list of data set variable names
                      with cardinality ratio and card-type
             purpose: provide for list processing routines
         -------------------------------------------------
Contexts: program group: procedure returns data set
          program  type: subroutine
          SAS      type: parameterized include procedure
          uses routines: n/a
----------------------------------------------------------
Specifications: input  : macro variables
                         libname: libref
                         memname: data set
                process: proc freq.n-levels
                         data: read var information
                               with scl functions
                               calculate cardinality ratio
                                     and card-ratio-type
                output : list_names, a dimension table
note: output is ordered by varnum
----------------------------------------------------------
usage:
autoexec:
filename site_inc '<...\SAS-site\includes>';
- - -  ...\SAS-site\sas-includes\put-global.sas - - -
%put _global_;
- - -  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;
%let cx_data    = work.list_names;
%let cx_include = site_inc(put_global)/source2;
%include site_inc(cx-inclu)/nosource2;
---------------------------------------------------------
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
---------------------------------------------------------
date-time: 8/26/2015 9:50:17 AM
word count        words:  514
                  lines:  138
characters(no   spaces): 3609
characters(with spaces): 5197
**** ..................... */
ODS  exclude all; * noprint;
**** make dimension table: name + n-levels;
PROC freq data    = &libname..&memname
          nlevels ;
          ods       output
          nlevels = out_n_levels
         (keep    =  tablevar nlevels
          rename  = (tablevar = name
                     nlevels  = n_levels));
run;
ODS select all;
%let _sys_last = &syslast;
 
****  make      mvars of n-obs and n-vars;
%let _dsid   = %sysfunc(open (&libname..&memname,i));
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
%let _rc     = %sysfunc(close(&_dsid));
%let _dsid   = %sysfunc(open (&syslast,i));
%let _n_vars = %sysfunc(attrn(&_dsid,nobs));
%let _rc     = %sysfunc(close(&_dsid));
%put info: &=_n_obs &=_n_vars;
%symdel _dsid _rc;
 
**** enhance dimension table with variable information,
     cardinality ratio (cr), card.ratio-type;
DATA list_names(label=
"list names &libname &memname nobs=&_n_obs vars=&_n_vars");
     attrib memname    length = $32
            varnum     length =   8 label = 'var num'
            name       length = $32 label = 'name'
            type       length = $ 1
            length     length =   8
            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"
            format     length = $48
          informat     length = $48
            label      length = $256;
     array  _cr(&_n_vars);
     drop   _:;       * _temporary variables;
     retain memname     "&memname"
            _max_length 0;
 
** read syslast, load array with card.ratio;
do _i = 1 to dim(_cr);
   set &syslast  (keep = n_levels)  point = _i;
   _cr(_i) = n_levels/&_n_obs;
   end;
 
***** calculate mean for card.ratio-type;
_mean_cr = mean(of _cr(*));
 
_dsid = open("&libname..&memname");
** read syslast, fetch info;
do _i = 1 to dim(_cr);
   set &syslast    point = _i;
   card_ratio = _cr(_i);
   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);
   ** for later use: max length of vartype=c;
   if type eq 'c' then
      _max_length = max(_max_length,length);
   select;
      when(n_levels   eq 1) cr_type = 'n-levels=1';
      when(card_ratio eq 1) cr_type = '.unique';
      when(card_ratio gt
                  _mean_cr) cr_type = 'many';
      otherwise             cr_type = 'few';
      end;
   output;
   end;
_rc = close(_dsid);
call symputx('_max_length_c',_max_length);
stop;
PROC delete data =    &_sys_last;
%symdel _n_obs _n_vars _sys_last;
run;
%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

• Cardinality_Ratio
• Journeymens_Tools
• GetFileInfo
• List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables
• Processing_Data_Sets
• Processing_Variables
• SmryEachVar_A_Data_Review_Suite
• Writing_Testing_Aware_Programs
• Multiple File Processing with SAS®, Kevin McGowan
• how to get list of folders and subfolders
• Obtaining A List of Files In A Directory Using SAS® Functions, Jack Hamilton

-- created by User:Rjf2 18 August 2008

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