Lesson 15, Part 4: Utility SAS Code

ProcDatasets.sas

Remove all labels and formats using the MODIFY statement and the ATTRIB option within PROC DATASETS.

proc datasets lib=work memtype=data;
     modify a;
     attrib _all_ format=;
contents data=a;
run;
quit;

Delete all SAS data sets from multiple libraries using the KILL option with PROC DATASETS

libname new ('C:\Data\Zip_folder' 'C:\Data\Xpt_folder' 
             'C:\Data\Cpt_folder' 'C:\Data\SDS_folder');
proc datasets nolist library=new kill;
quit;

Delete selected SAS data sets from the WORK library

proc datasets nolist lib=work;
 delete a1 a2 a3 ;
quit;

HowToDirList.SAS

Topic: How to create folders dynamically (Various)

%LET RootPath=c:\mydata;
%LET Folder=%SYSFUNC(DCREATE(MyNewFolder,&RootPath));
%LET RootPath=C:\; 
Data _Null_;
SubDIR='TeachingSAS';
FolderName=DCREATE(SubDIR,"&RootPath");
Put FolderName=;
Run;
%LET Folder=%SYSFUNC(DCREATE(MacroFolder,&RootPath)); 
%PUT Folder=&Folder;

%LET RootPath=c:\SASCourse;
%MACRO CF (start, stop);
  %DO num= &start %to &stop;
     %LET Folder1=%SYSFUNC(DCREATE(Week&num,&RootPath));
  %END; 
%MEND CF;
%CF(1,15)
%let TargetPath=c:\data\temp\Folder;
FILENAME FMyRep "&TargetPath";
%LET rc=%SYSFUNC(FDELETE(FMyRep));
FILENAME FMyRep CLEAR;

Turning external files into SAS® data sets: common problems and their solutions

Filename filelist pipe "dir /b /s c:\SASCourse\Week1\*.sas";  
   Data Listfiles;         
    Length very_last_word $8;
     Infile filelist truncover;
     Input filename $100.;
     very_last_word=scan(filename, -1);
     
     * Last words from the reverse direction delimited by a slash;
     File_name=substr(scan(filename, -1, '\'),1);
     Run; 
proc sort data=Listfiles; by File_name; run;
proc print data=Listfiles;
var  File_name;
where very_last_word eq 'sas';
run;

Filename filelist pipe "dir /b /s c:\temp\*.sas";                            
   Data _null_;                                        
     Infile filelist truncover;
     Input filename $100.;
     Put filename=;
 Run;

filename DIRLIST pipe 'dir /b /s c:\Data\*.sas';
data dirlist;
length filename $200;
infile dirlist length=reclen;
input filename $varying200. reclen;
run;

proc print data=dirlist; 
var file_name;
where scan(filename, -1, '.')='sas';
run;

Find the total number of observations from a SAS data set

%let dsn=sashelp.class;
%let dsid    = %sysfunc(open(&dsn,i)) ;
%let nobs    = %sysfunc(attrn(&dsid,nlobs));
%let dsclose = %sysfunc(close(&dsid));
%put &=nobs;

How to locate the library path

libname work list;
proc options option=work;
run;

%put %sysfunc(getoption(work));
%put %sysfunc(getoption(sasuser));

%put %sysfunc(pathname(work));
%put %sysfunc(pathname(sasuser));

HowToExtractDumpAttributesOfVarsOfInterest.sas

Retrieve attributes of variables (common prefix) by listing them as values of the NAME column through DICTIONARY.COLUMN.

libname PUFMEPS 'c:\Data\MySDS';
proc sql noprint;
select quote(strip(Name))
  into :varlist separated by ' '
  from dictionary.columns
 where libname= "PUFMEPS" and memname = upcase("h209")
    and name like "AD%";
 %let num_vars_AHS08=&sqlobs;
quit;

Dump the attributes of the variables of interest into a spreadsheet.

ods excel file = 'c:\Data\Var_Doc_Example.xlsx'
options(row_heights="0,0,0,14,0,0" sheet_interval='PROC'
                  sheet_name="AHS08" 
                  embedded_titles="yes");
proc sql;
title "Displaying attributes of %sysfunc(left(&num_vars_AHS08)) variables for Measure AHS08";
create table AHS08_vars as
select name, type, label
  from dictionary.columns
 where libname= "PUFMEPS" and memname = upcase("h209")
    and name in (&varlist)
		order by name;
  select monotonic() as Number, name, label
     from AHS08_vars;
quit;
ods excel close;

Retrieve attributes of variables by listing them as values of the NAME column through DICTIONARY.COLUMN

proc sql noprint;
select quote(strip(Name))
  into :varlist separated by ' '
  from dictionary.columns
 where libname= "PUFMEPS" and memname = upcase("h209")
    and name in ('DUPERSID', 'VARSTR', 'VARPSU');
 %let num_vars=&sqlobs;
quit;

Retrieve attributes of variables by listing them as values of the NAME column through DICTIONARY.COLUMN, where the values are the values of a macro variable.

%let mv=  VARSTR VARPSU perwt18f saqwt18f ADFLST42  AGELAST SEX RACETHX POVCAT18 INSCOV18 ADOFTB42 ADKALC42 ADHOPE42 ADINTR42 ADREST42 ADSAD42 ADMOOD42 ADNERV42 ADPRST42;
%let q_mv=%unquote(%str(%')%qsysfunc(tranwrd(%sysfunc(compbl(&mv)),%str( ),' '))%str(%'));
%put &q_mv;

proc sql noprint;
select quote(strip(Name))
  into :varlist separated by ' '
  from dictionary.columns
 where libname= "PUFMEPS" and memname = upcase("h209")
    and name in (&q_mv);
%let num_vars = &sqlobs;
quit;
%put &varlist;
%PUT &SQLOBS;
%put _user_;

Dump the attributes of the variables of interest into a spreadsheet.

ods excel file = 'c:\Data\Selected_SAQ_vars.xlsx'
options(row_heights="0,0,0,14,0,0" sheet_interval='PROC'
                  sheet_name="SAQ" 
                  embedded_titles="yes");
proc sql;
title "Displaying attributes of %sysfunc(left(&num_vars)) variables from MEPS-SAQ, 2018";
create table SAQ_vars as
select name, type, label
  from dictionary.columns
 where libname= "PUFMEPS" and memname = upcase("h209")
    and name in (&varlist)
		order by name;
  select monotonic() as Number, name, label
     from SAQ_vars;
quit;
ods excel close;