Tuesday, March 13, 2012

SAS Macro to Export All Datasets in Library as SPSS Files

/*********************************************
Export all datasets in library as SPSS
Looping code from Paper 93-26 by Edward Moore:
http://www2.sas.com/proceedings/sugi26/p093-26.pdf
Many thanks to the author.
*********************************************/
%MACRO exportSPSS(filepath, library);
/*----- Create Temporary Table -----*/
PROC SQL;
CREATE TABLE TempTable_sakg9389j AS
SELECT DISTINCT memname
FROM DICTIONARY.TABLES
WHERE LIBNAME="&library";
QUIT;


/*----- Scan through &tempListTable -----*/
DATA _NULL_;
IF 0 THEN SET TempTable_sakg9389j NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;


/*----- Loop -----*/
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET TempTable_sakg9389j (FIRSTOBS=&I);
CALL SYMPUT('tbl',COMPRESS(memname));
STOP;
RUN;
/* Export */
PROC EXPORT DATA= &library.&tbl 
            OUTFILE= "&filepath.\&tbl..sav"
            DBMS=SPSS REPLACE;
RUN;
%END;


/*----- Delete Temporary Table -----*/
PROC DATASETS noprint;
DELETE TempTable_sakg9389j;
QUIT;
%MEND exportSPSS;


%exportSPSS(C:\Users\USER_NAME\Documents, WORK);