Making Lists
From sasCommunity
Making Lists for List Processing
Contents |
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
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;
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
- Journeymens_Tools
- List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables
- SmryEachVar_A_Data_Review_Suite
- Writing_Testing_Aware_Programs
--macro maven == the radical programmer 11:13, 18 August 2008 (EDT)
- 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.
