Saturday, June 13, 2015

Two Methods to Create a CSV: Proc Export and the Data Step

Sample PROC Export procedure to export a SAS dataset to CSV:

proc export data=work.DATASET_NAME;
     outfile='c:\users\public\EXPORTED_FILE_NAME.csv'
     replace
     dbms=dlm;
     delimiter=',';
run;

Sample DATA Step to export a SAS dataset to CSV:

data _null_;
     set work.DATASET_NAME;
     file 'c:\users\public\EXPORTED_FILE_NAME.csv' dlm=',';
     put var1 var2 var3;
run;


The above two statements are essentially equivalent, with two exceptions: the data step does not output field names to the CSV file, and the data step requires that desired variables be explicitly included in the PUT statement.

Friday, March 20, 2015

SAS Macro to Print Record Counts for All Tables in a Library

/*----- PRINT RECORD COUNTS OF ALL TABLES IN LIBRARY -----*/
/* Looping code from Paper 93-26 by Edward Moore:
http://www2.sas.com/proceedings/sugi26/p093-26.pdf
Many thanks to the author. */
%MACRO countRecs(lib, tempListTable, tempOutputTable);
/*----- Make list of all tables in library -----*/
PROC SQL noprint;
CREATE TABLE &tempListTable AS
SELECT DISTINCT memname
FROM dictionary.tables
WHERE UPCASE(LIBNAME) = UPCASE("&lib");
QUIT;


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


/*----- Make table to hold record counts -----*/
DATA &tempOutputTable;
LENGTH tbl $29. ct 8;
RUN;


/*----- Loop -----*/
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET &tempListTable (FIRSTOBS=&I);
CALL SYMPUT('tbl',memname);
STOP;
RUN;
/* Populate datasets */
PROC SQL noprint;
INSERT INTO &tempOutputTable (tbl, ct) SELECT "&tbl" as tbl, COUNT(*) as ct FROM &lib..&tbl;
QUIT;
%END;


/*----- Print Result -----*/
PROC PRINT DATA = &tempOutputTable;
RUN;


/*----- Delete Temporary Tables -----*/
PROC DATASETS LIBRARY=work noprint;
DELETE &tempListTable &tempOutputTable;
QUIT;
%MEND countRecs;


%countRecs(WORK, kljgKGJ3208tjg, asvbjik4o9ksjd9);

Wednesday, March 18, 2015

SAS Macro to Import CSV Files into SAS


/***********************************************************
IMPORT CSV files into SAS Work Library
June 10, 2011
http://sastipsbyhal.blogspot.com/
*/
%LET file1 = myFirstFile.csv;
%LET file2 = mySecondFile.csv;
%LET file3 = myThirdFile.csv;
%LET file4 = myFourthFile.csv;
%LET file5 = myFifthFile.csv;
%LET file6 = mySixthFile.csv;
%LET file7 = mySeventhFile.csv;
%LET file8 = myEighthFile.csv;
%LET fileName1 = sasNameFor_myFirstFile;
%LET fileName2 = sasNameFor_mySecondFile;
%LET fileName3 = sasNameFor_myThirdFile;
%LET fileName4 = sasNameFor_myFourthFile;
%LET fileName5 = sasNameFor_myFifthFile;
%LET fileName6 = sasNameFor_MySixthFile;
%LET fileName7 = sasNameFor_mySeventhFile;
%LET fileName8 = sasNameFor_myEighthFile;
%LET fileLoc = \\folder\where\csv\files\are\located\;
%LET fileMax = 8;
%MACRO csvIMPORT;
%Do i = 1 %to &fileMax. ;
PROC IMPORT OUT=WORK.&&fileName&i 
            DATAFILE= "&fileLoc&&file&i"
            DBMS=CSV REPLACE;
    GETNAMES=YES;
    DATAROW=2; 
RUN;
%END;
%MEND;
%csvIMPORT;

Tuesday, March 17, 2015

Need help choosing data visualization colors? Try ColorBrewer2.org

Are you making a data visualization and interested in adding a custom set of colors to make the information really fly off of the page?

If so, I recommend checking out ColorBrewer2.org because it has all sorts of great tools for choosing colors, including color calculators.  It also has tips on choosing color combinations that are color-blind friendly, print friendly, and more.

Thursday, December 4, 2014

My favorite SAS proc: proc freq

No doubt, my favorite proc step is proc freq. It is extremely simple, but therein lies its beauty: it's fast to run and easy to understand. With just two lines of code (almost zero effort), it delivers a set of frequency output for all variables in one printout that anyone can understand, regardless of their involvement with the actual data set. I've found raw proc freq output to be a terrific starting point for conversations on data cleaning or data exploration, no matter who is involved in the conversation:

PROC freq data=NAME;
run;