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.

Difference between revisions of "Making Lists"

From sasCommunity
Jump to: navigation, search
m (References added sesug.2007.McGowan)
m (References stackoverflow)
Line 510: Line 510:
** Kevin McGowan
** Kevin McGowan
-- created by [[User:Rjf2]] 18 August 2008
-- created by [[User:Rjf2]] 18 August 2008

Revision as of 07:48, 13 May 2015

Making Lists for List Processing

Author: Ronald_J._Fehd

keywords: control data set

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

filename SiteIncl '.';
*filename SiteIncl '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: MlFiles-caller;
%Let MlFolder = C:\temp;
%Let MlOut    = Work.FileNames;
%Include SiteIncl(MlFiles);
Proc Print data = &SysLast.;
* name       : MlFiles;
* 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 SiteIncl(MlFiles);
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: MlFolders-caller;
%Let MlFolder = C:\;
%Let MlOut    = Work.Folders;
%Include SiteIncl(MlFolders);
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 SiteIncl(MlFiles);
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;

Make List of Libnames

   describe table Dictionary.Libnames;
   create table Work.ListLibnames as
      select *
      from Dictionary.Libnames
* name: MlLibname-caller;
%Let MllPrefix = SAShelp;
%Include SiteIncl(MlLibnames);
Proc Print data = &SysLast.;
* name: MlLibnames;
   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.LibraryMembers;
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=LibraryMembers;

Proc SQL

* name: MlMemnames-caller;
%Let MlmLibname = Library;
%Let MlmLibname = SAShelp;
%Include SiteIncl(MlMemnames);
Proc Print data = &SysLast.;
* name: MlMemnames;
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: MlNames-caller;
%Let MlnLibname = Library;
%Let MlnLibname = SAShelp;
%Let MlnMemname = _all_;
%Let MlnMemname = Class;
%Include SiteIncl(MlNames);
Proc Print data = &SysLast.;
* name: MlNames;
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: MlNames-caller;
%Let MlnLibname = Library;
%Let MlnLibname = SAShelp;
%Let MlnMemname = Class;
%Include SiteIncl(MlNames);
Proc Print data = &SysLast.;
* name: MlNames;
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.

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: MlSheets;
%*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: MlSheets-caller;
%Let MlfFolder = ..\xls;
%Let MlfOut     = Work.ListXls;
%Include SiteIncl(MlFiles);
%Let MlxFolder = ..\xls;
%Let MlxOut     = Work.ListSheets;
%Include SiteIncl(MlSheets);
%Let CxData    = &MlxOut.;
%Let CxInclude = SiteIncl(MlSheets);* returns Work.ListSheets
%Include SiteIncl(MlFiles);
%Let CxData    = &MlfOut.;
%Let CxInclude = SiteIncl(ImportXls);
%Include SiteIncl(MlFiles);
%Let MlsLibname = SAShelp;
%Let MlsMemname = Class;
%Let MlsName    = Sex;
%Let MlsOut     = Work.ListSheets;
%Include SiteIncl(MlSheets);
Proc Print data = &SysLast.;

Make List of Subsets

Proc Freq

* name: MlsFreq-caller;
%Let MlsLibname = Library;
%Let MlsLibname = SAShelp;
%Let MlsMemname = Class;
%Let MlsName    = Sex;
%Let MlsOut     = Work.ListSexes;
%Include SiteIncl(MlsFreq);
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: MlsSort-caller;
%Let MlsLibname = Library;
%Let MlsLibname = SAShelp;
%Let MlsMemname = Class;
%Let MlsNames   = Sex;
%Let MlsOut     = Work.ListSexes;
%Include SiteIncl(MlsSort);
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)