I wrote this to do the following: 1. Find all fields in a dataset with a length over 8 bytes. 2. For each of them, count the distinct values in the dataset. 3. If there are at least 10 records per value, create a surrogate key table for the field and replace the field with the key in a copy of the dataset.   There's also a 3rd copy of the database that has the field and the key in it.   If you're comfortable with the results, you can then delete the original dataset as well as the 3rd version.  I didn't delete them because I wanted to show the difference in size.   For example, one dataset started out at 3,491,692,544 bytes with 6 fields converted to keys resulting in a normalzed dataset at 2,379,612,160 with 6 key tables (23,461,888) for a saving of 31%.     %macro normalize(lib,dsn);     PROC SQL;         SELECT name into :name1 -             FROM dictionary.columns         WHERE compress(libname||'.'||memname) = "&lib..&dsn" AND length > 8;     quit;     %if &SQLOBS = 0 %then %return;     %let reccnt = &sqlobs;     PROC SQL;         CREATE TABLE &lib..&dsn.2 AS SELECT * FROM &lib..&dsn;         QUIT;     %do i = 1 %to &reccnt;         %put &&name&i;         proc sql;             select count(*),COUNT(DISTINCT(&&name&i)),int(count(*)/COUNT(DISTINCT(&&name&i)))             into :count,:countdist,:pct             from &lib..&dsn;         quit;         %put &=count &=countdist &=pct;         %if &pct > 10 %then %do;             proc sql;                 CREATE TABLE work.distinct AS                 SELECT DISTINCT &&name&i                 FROM &lib..&dsn;             quit;             data &lib..sk_&&name&i; set work.distinct; sk_&&name&i =_n_; run;               PROC SQL;                 CREATE TABLE &lib..&dsn.3 AS                     SELECT t2.*, t1.sk_&&name&i                     FROM &lib..sk_&&name&i t1                         INNER JOIN &lib..&dsn.2 t2 ON (t1.&&name&i = t2.&&name&i);             QUIT;             data &lib..&dsn.2; set &lib..&dsn.3; drop &&name&i; run;           %end;      %end;  %mend normalize;   ... View more

If you need to save all your libnames and then be able to restore them later, here's how I do it:   /* save libnames  somewhere...I use the program's current path with the date and userid subdirectories*/ proc sql; create table foo as select distinct 'libname ' || libname || ' "' || trim(path) || '";' as libname format $500. from sashelp.vlibnam where libname not in ('MAPSGFK','MAPSSAS','SASHELP','WORK');quit; data _null_; file "&CURRENTPATH.&YMD8/&SYSUSERID/libnames.sas"; set foo; libname = trim(libname); put libname; run;   Then in some other program (or, for parallel projects, I put it in the 'insert custom code before submitted code'):   %include "&CURRENTPATH.&YMD8/&SYSUSERID/libnames.sas";   ... View more

I thought I'd share a technique for anonymizing Personally Identifiable Information data while maintaining field lengths (random x digit number):   Found this macro at https://blogs.sas.com/content/iml/2015/10/05/random-integers-sas.html   %macro RandBetween(min, max); (&min + floor((1+&max-&min)*rand("uniform"))) %mend;   and so this is what I came up with to randomize PII (claim numbers, medicaid IDs, NPIs)   proc sql; create table foon as SELECT %RandBetween(10**12, (10**13)-1) as CLCL_ID, t1.hcas, t1.hcss, t1.hcep, t1.hcaps, %RandBetween(10**13, (10**14)-1) as MEDICAID_ID, t1.lbpi, %RandBetween(10**11, (10**12)-1) as BILL_PROV_NPI, %RandBetween(10**11, (10**12)-1) as SERV_PROV_NPI, t1.MIN_of_Line_FROM_DT, t1.MAX_of_Line_TO_DT, t1.CLCL_RECD_DT, t1.dcabtmco, t1.dcpbtm, t1.SUM_of_CDML_CHG_AMT, t1.SUM_of_CDML_PR_PYMT_AMT FROM WORK.FOO t1; QUIT;   Just thought I'd pass it on...maybe it'll be of use to others. ... View more