topic macro in SAS Programming

macro

googa22 — Wed, 15 Jul 2015 14:32:40 GMT

I want to do a macro which search in every folder of a directory and when he finds a sas table in that folder to do a proc cporc. Any ideas?

Re: macro

Reeza — Wed, 15 Jul 2015 15:05:43 GMT

Use your OS listing with the recursive option to list every file/folder in the directory and bring that into a table. Then use call execute or a macro to do your proc.

Re: macro

googa22 — Wed, 15 Jul 2015 15:23:55 GMT

Thank you! I will try.

Re: macro

googa22 — Wed, 15 Jul 2015 15:49:14 GMT

My OS is Linux and I made this to obtain the table with paths:

data _null_;

rc1=system("cd /data/projects/smartdata/librerie/");

rc2=system("du -k > /data/projects/smartdata/librerie/listfolders.txt");

run;

PROC IMPORT OUT= WORK.listfolders

DATAFILE= "/data/projects/smartdata/librerie/listfolders.txt"

DBMS=DLM REPLACE;

DELIMITER='09'x;

GETNAMES=NO;

DATAROW=1;

RUN;

And I have also this macro:

%macro list_folder_contents(folder_path,file_path);

*create library;

libname lib_tmp "&folder_path.";

*list library detailed contents;

proc sql;

create table List_lib_contents as

select

memname,

memtype

from dictionary.members

where libname="LIB_TMP" and memtype in ('DATA','CATALOG');

quit;

*count the number of rows;

data _NULL_;

if 0 then set work.List_lib_contents nobs=n;

call symputx('nrows',n);

stop;

run;

*verify for files;

%if &nrows > 0 %then %do;

libname oldlib "&folder_path.";

filename tranfile "&file_path.";

proc cport lib=oldlib file=tranfile;

run;

%end;

%mend;

Can I call this macro using perhaps like parameter the paths which are in the sas table obtained?

Re: macro

Reeza — Wed, 15 Jul 2015 16:22:39 GMT

Sure, see my working example on GitHub, and specific to your situation below. I don't see (or missed) how filepath is specified in your macro or data so you'll need to work that out as well. The actual macro execution is commented out, I like to verify my macro call first and then run it.

SAS - Call macro using parameters from data set

data sample;

set listfolders;

string = catt('%list_folder_contents(age=' folder_path, ',', filepath, ');');

put string;

*call execute(string);

run;

Re: macro

Ron_MacroMaven — Wed, 15 Jul 2015 20:52:47 GMT

to make a list of SAS data sets, use proc contents, which requires a libname statement for the listing.

since you want to search a folder and sub-folders

check this page on making lists.

http://www.sascommunity.org/wiki/Making_Lists

you need to figure out which way you are going to do these tasks.

in order to use cport you need a libref.data_set_name.

your Q implicitly stated that you wanted to search for files named data_set_name.sas7bdat

and then automagically cport them.

doing a recursive call to a make-list-folders is non-trivial.

I would use a DOS directory command to find all the folders containing *.sas7bdat

then parse that to a set of folders that are arguments to the libname::libref statements for use by contents

Here is a tool to call a macro using a data set as list of parameter values.

Macro CallMacr - sasCommunity

1. make list of folders

dir *.sas7bdat /S >list-of-folder-with-data-sets.txt

2. edit that file and get one row per folder, save as list-folders.csv

folder

c:\temp\sas7b

3. import list-folder.csv to data-set list_folders

4. write and test macro cport with parameters data and transport-file-name

5. write and test macro make_list_folders_data with parameters folder

%macro make_libref(folder=c:\temp)

libname temp "&folder";

proc contents data = temp._all_

out = out_contents;

DATA out_list_data;

attrib folder length = $256;

retain folder "&folder";

set out_contents;

proc append data = &syslast

base = list_folders_data_sets;

run;

libname temp clear;*important! libref TEMP is reused;

run;

%mend;

this macro is called for each row of list_folders

6. use data set list_folders_data_sets to call macro cport

ah, yes, there is a slight problem with naming collisions in the cport transport filenames.

every data set ought to be written to a same-name-as-data-set transport file

that is easy to guarantee when reading from one folder

but not, in your case, multiple folders which may contain same names in different folders.

one solution to consider: sequentially number the transport files with either a prefix or suffix

your cport macro can increment a global macro variable

%let cport_N = 1;

%macro cport(libref=temp,data=);

*...;

%global cport_N;

%let cport_N = %eval(&cport_N+1);

%mend;

hth

Re: macro

googa22 — Thu, 16 Jul 2015 13:58:41 GMT

Thank you all for answers. They have been helpful to me.

Re: macro

ChrisNZ — Thu, 16 Jul 2015 23:14:32 GMT

Mark them as such then.