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

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

Making Lists

From sasCommunity
Revision as of 10:08, 9 November 2017 by Paulkaefer (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Making Lists for List Processing

Author: Ronald_J._Fehd

keywords: control data set

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

filename site_inc '.';
*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;

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);
rc = dclose(did);

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=;
*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 &  ;
proc print;

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;

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;

Make List of Libnames

PROC SQL; describe table dictionary.libnames;
          create table work.listlibnames as
          select *
          from dictionary.libnames;
* 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.%)"

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;
ods listing select none;
ods output members = &out
          (label   = "members of &libref library");
PROC datasets library = &libref;
ods output close;
ods listing select all;
PROC print data=library_members;

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

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.
              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';

For usage see Processing_Variables.

MWSUG.2016.TT03: 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\
     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
                           for data structure attrib
                           valu_c length=$&_max_length_c
note: output is ordered by varnum
usage:   autoexec:
filename site_inc '<...\SAS-site\includes>';
- - - - - -
%let libname = sashelp;
%let memname = class;
%include site_inc(ml-namex);
proc sql; describe table list_names;
proc print data = list_names;
this is a Derivative Work of the SmryEachVar suite
this program is available on: 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));
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=
     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(
           _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;
***** 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);
     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;
   ** 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);
   else if cr_type eq 'few'  then do;
      **** save for sort: lfs(i)= '000:name';
      _lfs(_i) = catx(':',put(n_levels
      *** add one for delimiter = space;
      _length_few = sum(1,_length_few);
      if type eq 'c' then do;
      else _length_few=sum(_length_few,%length(&_n_obs));
   else if cr_type eq '.unique'  then
       _list_unique = catx(' ',_list_unique,name);
_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);
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 );
%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

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.

  • 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


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;

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 )
%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";
%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;
* 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:;
Proc Freq data   = &MlsLibname..&MlsMemname;
          tables   &MlsName
                 / list missing noprint
             out = &MlsOut;

Proc Index

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:;
proc sort data = &mlslibname..&mlsmemname.
         (keep = &mlsnames)
          out  = &mlsout
          by     &mlsnames;

Proc SQL

* name:;
select distinct ...;

Proc Summary

* name:;

Proc Univariate

* name:;


-- created by User:Rjf2 18 August 2008

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