Month: November 2013

Get overview of distinct values in dataset

The code below will make distinct values of all the variables in a dataset and list them besides each other for a better overview.

%let Delimitor = ¤;

%macro PrepareData(InLib=, Inds=);
 %let InLib = %upcase(&InLib);
 %let InDS = %upcase(&InDS);

 proc sql noprint;
  create table CON_&InDS. as
   select *
   from dictionary.columns
   where upcase(LibName) eq "&InLib." and upcase(MemName) eq "&InDS.";
  quit;

  %global Columns;

  proc sql noprint;
   select name into :Columns separated by "&Delimitor"
   from CON_&InDS.;
  quit;

  %put Columns: &Columns;

  proc sql noprint;
   create table RES_&InDS. like &InDS.;
  quit;

  data RES_&InDS. (drop = i);
   do i = 1 to 200;
    ID + 1;
    output;
   end;
   set RES_&InDS.;
  run;
%mend;

%macro MakeDistinct(InDS=,OutDS=);
 %let NumberOfColumns = %sysfunc(countw(&Columns., &Delimitor.));
 %put Number of columns: &NumberOfColumns.;

 %do J=1 %to &NumberOfColumns.;
  %let Column = %scan(&Columns., &J, &Delimitor.);
  %put Processing: &Column.;

  proc sql noprint;
   create table tmpDS as
    select distinct(&Column.) as &Column.
    from &InDS.;
  quit;

  data tmpDS;
   ID + 1;
   set tmpDS;
  run;

  data &OutDS.;
   merge &OutDS. (in=a) tmpDS (in=b);
   by id;
  run;
 %end;
%mend;

%PrepareData(InDS=sashelp, Inds=class);
%MakeDistinct(InDS=class, OutDS=Result_Class);

Copy .sas files (program files) with SAS

The code below copies all the SAS-programs (*.sas) files in a directory to another directory. This solution should be used if you don’t want to use an OS-command that copies the files. Using an OS-command is a lot easier and doesn’t require as much code. But of course depends on the OS your running on. This solution is OS independent.

%let SourcePath = C:\test\source;
%let DestinationPath = C:\test\destination;

/*
Reads the .sas files in &location.
*/
%macro GetFilenames(location);
 filename _dir_ "%bquote(&location.)";
 data filenames(keep=memname);
  handle=dopen( '_dir_' );
  if handle > 0 then do;
   count=dnum(handle);
   do i=1 to count;
    memname=dread(handle,i);
    output filenames;
   end;
  end;
  rc=dclose(handle);
 run;

 filename _dir_ clear;
%mend;

%GetFilenames(&SourcePath.);

/* We only want SAS-files and not SAS-datasets. */
data filenames;
 set filenames;
 if index(memname, '.sas') eq 0 or index(memname, '.sas7bdat') eq 1 then delete;
run;

%let Delimitor = ¤;

proc sql noprint;
 select memname into :Files separated by "&Delimitor"
 from FileNames;
quit;

%put Files to copy: &Files;

%macro CopySASFiles;
 %let NumberOfFiles = %sysfunc(countw(&Files., &Delimitor.));
 %put Number of files: &NumberOfFiles.;

 %do J=1 %to &NumberOfFiles.;
  %let File = %scan(&Files., &J, &Delimitor.);
  %put Copying SAS-program: &File.;

  data _null_;
   infile "&SourcePath.\&File."  lrecl=32767;
   file "&DestinationPath.\&File.";
   input;
   put _infile_;
  run;
 %end;
%mend;
%CopySASFiles;

 

Commenting in SAS

I think the best way to start a SAS-program is to do a comment as described by the template below.

/********************************************************************************
Author        : 
Creation date : ddmmmyyy
Description   : 
Example       :
*********************************************************************************
Input
-----
&InputMacro   : Macrovariable with inputs.
InputDS       : Tells what dataset to do the processing on.
*********************************************************************************
Output
------
&OutputMacro  : Macrovariable with the output result.
OutputDS      : Dataset with the output result.
********************************************************************************/

Comments should also be done above each datastep or procedure, And changes to the programs should be contained in a versioning system eg like Subversion (SVN).If you do not have a versioning system, then I think the comments should be something like the comments below.

/* AUTHOR <ISODATE>: Changed one thing to another. */