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
Making Lists for List Processing
Author: Ronald_J._Fehd
keywords: control data set
Contents
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)