Hello Tahir, and Dave, this may be for you too,

What you need is a method to determine the available datasets in a library automatically. I have developed several methods, which sometimes depend on which file types to handle. Of those you might need method 1b (1a for Dave), combined with either of the following step methods. The determination of the number of observations in each dataset actually should be the processing of the file list. I'll leave incorporating you code into the code below up to you, incl. the generation of as many number (macro) variables as there are datasets (though you might build a normal dataset with the dataset names and their observation numbers.

I have presented the methods below in August 2002 for the last time to SAS-L.

The various goals and their methods are: 1a. obtaining a list of existing files using a pipe construct (any files with wildcard) 1b. obtaining a list of existing SAS files (e.g. datasets) using PROC DATASETS 2a. storing the file list in a single macro variable 2b. storing the file list in a dedicated SAS dataset (as records) 3a. processing the file list in the macro variable using a macro, processing the file 3b. processing the file list in the dataset using a macro, processing the file 3c. processing the file list in the macro variable with a data step, processing the file 3d. processing the file list in the dataset with a data step, processing the file

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~; * Determine the dataset names, which exist, automatically; *________________________________________________________;

%PUT Method 1a, involving a pipe and creating a macro variable with a dataset list; * (Piping occasionally crashes the SAS process in my PC configuration);

* Build macro variable DsetList and ListSize; %LET DsetList = ; %* Initialize empty; FILENAME FileList PIPE "DIR /B/ON E:\&WildCard..SD2"; * SAS vs. 6.12; DATA _NULL_; LENGTH DsetName $100; INFILE FileList; INPUT DsetName; * Now remove .SD2 extension; DsetName = SUBSTR (DsetName, 1, INDEX (UPCASE(DsetName), ".SD2") - 1); IF (UPCASE(DsetName) NE "EXCLFILE"); * Exclude this file name; ListSize + 1; CALL SYMPUT ('ListSize', TRIM (LEFT (PUT (ListSize, BEST12.)))); CALL EXECUTE ('%LET DsetList = &DsetList ' || DsetName || ';'); RUN; %PUT DsetList=&DsetList;

%PUT Method 1b, involving a pipe and creating a dataset with the dataset names;

* Build macro variable DsetList and ListSize; FILENAME FileList PIPE "DIR /B/ON E:\&WildCard..SD2"; * SAS vs. 6.12; DATA DsNames1; LENGTH DsetName $100; INFILE FileList; INPUT DsetName; * Now remove .SD2 extension; DsetName = SUBSTR (DsetName, 1, INDEX (UPCASE(DsetName), ".SD2") - 1); IF (UPCASE(DsetName) NE "EXCLFILE"); * Exclude this file name; RUN;

%PUT Method 2a, w/ PROC DATASETS and creating a macro variable with a dataset list; * This does not apply to listing other files than various SAS files;

PROC DATASETS LIBRARY=Lib NOLIST; CONTENTS DATA=_ALL_ OUT=work.Scratch (KEEP=MemName) NOPRINT NODETAILS; RUN; PROC SORT DATA=Scratch NODUPKEY; BY MEMNAME; RUN;

%LET DsetList = ; %* Initialize empty; DATA _NULL_; SET Scratch; IF (UPCASE(MemName) NE "EXCLFILE"); * Exclude this file name; IF (SUBSTR(UPCASE(MemName),1,7) NE "&WildCard"); * Include &WildCard..sd2; ListSize + 1; CALL SYMPUT ('ListSize', TRIM (LEFT (PUT (ListSize, BEST12.)))); CALL EXECUTE ( '%LET DsetList = &DsetList ' || MemName || ';' ); RUN; %PUT DsetList=&DsetList;

%PUT Method 2b, w/ PROC DATASETS and creating a dataset with the dataset names; * This does not apply to listing other files than various SAS files;

PROC DATASETS LIBRARY=Lib NOLIST; CONTENTS DATA=_ALL_ OUT=work.Scratch (KEEP=MemName) NOPRINT NODETAILS; RUN; PROC SORT DATA=Scratch NODUPKEY; BY MEMNAME; RUN;

DATA Dsnames2 (RENAME=(MemName=DsetName)); SET Scratch; IF (UPCASE(MemName) NE "EXCLFILE"); * Exclude this file name; IF (SUBSTR(UPCASE(MemName),1,2) EQ "D_"); * Include &WildCard..sd2; RUN;

%PUT Given (arbitrary) processing macro Process; %MACRO Process (DataSet); %PUT DataSet=&DataSet; %* More macro code, executed directly in the calling data step; DATA DataNew; SET Lib.&DataSet; * Some example processing code; Date = TODAY(); FORMAT Date DATE9.; * End of example processing code; RUN; TITLE1 "Macro Process, Print of DataNew, originating from &DataSet"; TITLE2 "Macro Process called from &CalledBy"; PROC PRINT DATA=DataNew; RUN; %* More SAS data step or procedure code, executed delayed, after finishing the calling data step; %MEND Process;

%PUT Process 3.a; * Processing the dataset names from the macro variable list with a macro;

%MACRO ForEach1; %LOCAL I DsetName CalledBy; %LET CalledBy = ForEach1; %LET I = 1; %LET DsetName = %SCAN(&DsetList, &I, %STR( )); %DO %WHILE (&DsetName NE); %Process (&DsetName); %LET I = %EVAL(&I+1); %LET DsetName = %SCAN(&DsetList, &I, %STR( )); %END; %MEND ForEach1;

%ForEach1;

%PUT Process 3.b; * Processing the dataset names from the created dataset with a macro;

%LET CalledBy = Process 3.b; DATA _NULL_; SET DsNames2; CALL EXECUTE ( '%Process (' || DsetName || ');' ); RUN;

%PUT Process 3.c; * Processing the dataset names from the macro variable list in a data step;

%MACRO ForEach2; %LOCAL I; %LET I = 1; %LET DataSet = %SCAN(&DsetList, &I, %STR( )); %DO %WHILE (&DataSet NE); %* Data step code from macro %Process; DATA DataNew; SET Lib.&DataSet; * Some example processing code; Date = TODAY(); FORMAT Date DATE9.; * End of example processing code; RUN; TITLE "Macro Foreach2, Print of DataNew, originating from &DataSet"; PROC PRINT DATA=DataNew; RUN; %LET I = %EVAL(&I+1); %LET DataSet = %SCAN(&DsetList, &I, %STR( )); %END; %MEND ForEach2;

%ForEach2;

%PUT Process 3.d; * Processing the dataset names from the created dataset in a data step;

DATA _NULL_; SET DsNames2; put "Before CALL EXECUTE (1)" DsetName=; CALL EXECUTE ( ' DATA DataNew; SET Lib.' || DsetName || '; Date = TODAY(); FORMAT Date DATE9.; RUN; TITLE "Data step 3.d"; PROC PRINT DATA=DataNew; RUN; '); * Such code may easily get too long to be admitted, delete the comment; put "Behind CALL EXECUTE (1)" DsetName=; RUN;

DATA _NULL_; SET DsNames2; put "Before CALL EXECUTE (2)" DsetName=; %* Data step code from macro %Process; CALL EXECUTE ( 'DATA DataNew;'); CALL EXECUTE ( 'SET Lib.' || DsetName || ';'); * Some example processing code; CALL EXECUTE ( 'Date = TODAY();'); CALL EXECUTE ( 'FORMAT Date DATE9.;'); * End of example processing code; CALL EXECUTE ( 'RUN;'); CALL EXECUTE ( 'TITLE "Data step 3.d via separate CALL EXECUTEs";'); CALL EXECUTE ( 'PROC PRINT DATA=DataNew;'); CALL EXECUTE ( 'RUN;'); put "Behind CALL EXECUTE (2)" DsetName=; RUN; %PUT After data step 3.d;

Variants of the PIPE construct apply an intermediate scratch file to write the file names to (with a DOS command without PIPE). You may also consider the use of the FILEVAR= option with raw INFILE data; several other contributors have suggested and explained that situation. I'll leave this just a hint.

I have seen even more differing methods, involving a.o.: - FILENAME without PIPE, representing a directory name - INFILE wildcard (with asterisks), thus not a single file spec.

And Dennis Diskin just suggested even another way of doing (SASHELP.VTABLE). I have yet to experiment myself with these methods and add them to my overview of methods.

Regards - Jim. -- Y. (Jim) Groeneveld, MSc IMRO TRAMARKO tel. +31 412 407 070 senior statist./data man. P.O. Box 1 fax. +31 412 407 080 J.Groeneveld@ITGroups.com 5350 AA BERGHEM, NL www.imrotramarko.com

IF Annoying=T THEN Salutation=''; ELSE IF Sex='M' THEN Salutation='Hi'; ELSE IF Sex='F' THEN Salutation='Dear'; ELSE /* unknown, more, all */ Salutation='Hallo';

Notice of confidentiality: this e-mail may contain confidential information intended for the addressed recipient only. If you have received this e-mail in error please delete this e-mail and please notify the sender so that proper delivery can be arranged.

> -----Original Message----- > From: Saqi [mailto:saqi2000@TOTALISE.CO.UK] > Sent: 11 February 2003 13:53 > To: SAS-L@LISTSERV.UGA.EDU > Subject: How to count observation? > > > Hi their, > > I have to count number of observations in each dataset in > library called > departments. I need to count number of employee in each > department. I have got > this macro to do the job. > > %macro dsnobs(dsn); > %global nobs; > %let nobs=; > data _null_; > if 0 then set &dsn nobs=count; > call symput('nobs', left(put(count, 8.))); > stop; > run; > %mend dsnobs; > > but the problem is I have to run over library departments how > would I do that > so it goes and searches the department library and counts number of > observation in each data set and returns the name of the data > set and number > of observations. > > > > Could anyone help please? > > Many thanks > > Tahir

> -----Original Message----- > From: David Treder [mailto:dtreder@GENESEEISD.ORG] > Sent: 10 February 2003 23:01 > To: SAS-L@LISTSERV.UGA.EDU > Subject: Combining Files into a single Dataset > > > I would greatly appreciate any help anyone could provide: > > I have 600 files, each with a unique five-digit name (10010, > 24010, etc. > - I can sort them, but they aren't sequential). Each file > has the same > format: 20 variables, comma delimited, no header row. > > Culd someone possibly suggest a way to set up a macro to read > all these > files into a single SAS dataset? > > Thanks again, > Dave >