Pick up sas dataset name automatically from folder

[deleted_user]

Hi all,

I have a project to do where I have an excel spreadsheet which has ID and one more column of data. I can create a sas dataset from excel spreadsheet which will have 2 variables by importing from excel to sas. I have more than 50 sas datasets in one folder. I have to add this column to all the sas datasets. I can merge the column using ID number. I can write a merge data step macro like below:

%macro jig (name= );

data datdir.NEW_&name ;
merge excelsheetdata datdir.&name ;
by ID;
run;

%mend jig;

%jig (name=AB);
%jig (name=AC); and so on for 50+ times..........................

I have to write these datasets name manually. Is there any programming flexibility in SAS where SAS code can pick up these dataset names directly from the folder where these datasets are located rather than me passing 50+ names manually in a macro call.

Thanks,

Jig.

[deleted_user]

Sure, SAS supports the use of wildcards in file and directory names.

You will need to read the SAS documentation.

An alternative is to write a macro that creates a list of files in a dataset, which can then be used for subsequent processing. This can be done the "dopen" function and subsequent "d..." functions.

If you need the explicit filename used, in a datastep, when reading the EXCEL spreadsheet, then you can use the "filevar=" option.

[deleted_user]

Hi Jigar

as I understand it, your NAME= parameter is one of many SAS data sets in a folder. That folder needs to be assigned as a SAS library, like [pre] libname jig 'that folder' ;[/pre]Then with the internal SAS dictionary.table you can use the names of the SAS data sets in that JIG library as a variable. Try :[pre]
%macro jig( name );
data jig.NEW_&name ;
merge excelsheetdata datdir.&name ;
by ID ;
run ;
%mend jig;

*invoke macro JIG for each data set in JIG library;
* 1 create table of data set names in library JIG ;
proc sql ;
create table jigsDs as select memname from sashelp.vtable
where libname='JIG' and nobs GT 0 ;
quit;
* 2 use that data set to invoke the macro once for each DS ;
data _null_ ;
set jigsDS ;
call execute( '%nrstr( %%)' !! 'jig(' !! memname !! ') ' ) ;
run;[/pre]

try that.

PeterC

[deleted_user]

Thanks Peter.

This works great...

Jig.