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.


Making Lists

From sasCommunity
Revision as of 08:13, 5 November 2010 by Rjf2 (Talk | contribs)

Jump to: navigation, search

Making Lists for List Processing

autoexec.sas

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

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

	 &List.;
         quit;

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

see also macro DIRLISTWIN: http://support.sas.com/kb/24/addl/fusion24820_1_dirlistwin.sas.txt

see also: GetFileInfo

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;
   end;
stop;
run;

Make List of Libnames

PROC SQL; 
   describe table Dictionary.Libnames;
   create table Work.ListLibnames as
      select *
      from Dictionary.Libnames
   ;
quit;
* name: MlLibname-caller;
%Let MllPrefix = SAShelp;
%Include SiteIncl(MlLibnames);
Proc Print data = &SysLast.;
* name: MlLibnames;
PROC SQL;
   create table Work.ListLibnames as
      select *
      from Dictionary.Libnames
      where Libname like "%upcase(&MllPrefix.%)"
   ;
quit;

Make List of Memnames

Proc Contents

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

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.
                       noprint
              out    = Work.ListNames
             (keep   = Libname Memname Name Type Length Label VarNum
              where  = (MemType eq 'DATA') );

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

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

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

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: MlsSort.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

--macro maven == the radical programmer 11:13, 18 August 2008 (EDT)