VarMatch Macro

Have you ever wanted to select variables based off of the leading or ending character(s)? When some set of leading character(s) is wanted the Colon comes in very handy.

Data Need ;
Set Have ( Keep = Var: ) ;
<Some SAS Code>  
Run ;

This Keeps all variables starting with 'Var'.

However, there is no such trick to grab only variables that end in a certain set of character(s). To that end I created the VarMatch macro which will not do both but allows for other crazy pattern you may or may not want to match variable names with.

%Macro VarMatch( DSN = , Pattern = , PRX= ) ;
/****************************************************************************/
/** Macro Name : VarMatch                                                  **/
/**                                                                        **/
/**                                                                        **/
/** Purpose    : Returns A List Of Variables From A Data Set That Matces A **/
/**              Given Pattern.                                            **/
/**                                                                        **/
/** Parameters : DSN ~ Data Set To Get Variables From.  It Can Be Either   **/
/**                    Just A Member Name Which Cause The Macro To Look    **/
/**                    In The Work Directory Or A Two Level Name.          **/
/**                                                                        **/
/**              Pattern ~ Is A Simple Pattern Where The User Gives The    **/
/**                        Known Letters in The Variables Name Seperate By **/
/**                        * For Those They Do Not.                        **/
/**                        Examples: ABC* = All Vars That Start With ABC   **/
/**                                  *ABC = All Vars That End With ABC     **/
/**                                  A*C  = All Vars That Start With A And **/
/**                                         End With C.                    **/
/**                                                                        **/
/**              PRX ~ Is A Perl Regular Expression.  This Is Designed For **/
/**                    Advanced Users Who Fully Understand RegEx.          **/
/**                                                                        **/
/** Created By: Toby Dunn                              *****************   **/
/** Created On: 03/12/2008                             * Make It Right *   **/
/**                                                    *****************   **/       
/****************************************************************************/

%Local DSID VarNum I VarName Pattern VarList Close ;

%If ( %Length(&DSN) EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put ERROR: The DSN Parameter Is Empty. ;
  %Put ERROR: Please Specify A Valid Value.  ;
  %Put ;
  %Put ;
  %Return ;
%End ;

%If ( %SysFunc( Exist( &DSN ) ) EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put ERROR: The Data Set [&DSN] Does Not Exist!!! ;
  %Put ERROR: Please Check The Value Of DSN= Parameter ;
  %Put ;
  %Put ;
  %Return ;
%End ;

%If ( %Length( &Pattern ) EQ 0 ) And 
    ( %Length( &PRX ) EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put ERROR: There Is No Pattern Or RegEx To Match. ;
  %Put ERROR: Please Specify A Valid Value For Pattern Or PRX.  ;
  %Put ;
  %Put ;
  %Return ;
%End ;


%If ( %Length( &Pattern ) GT 0 ) And 
    ( %Length( &PRX ) GT 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put NOTE: A Value Was Given For Both Pattern And PRX. ;
  %Put NOTE: The PRX RegEx Will Be Used.  ;
  %Put ;
  %Put ;
%End ;


%Let DSID = %Sysfunc( Open( &DSN , I ) ) ;   

%If ( &DSID EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put ERROR: Data Set [&DSN] Could Not Be Opened. ;
  %Put %SysFunc( SysMSG() ) ; 
  %Put ;
  %Put ;
%End ;


%Let VarNum  = %SysFunc( AttrN( &DSID , NVars ) ) ;                                                                                                 

%If ( &VarNum EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put ERROR: Data Set [&DSN] Has No Variables. ;
  %Put ;
  %Put ;
%End ;



%If ( %Length( &PRX ) > 0 ) %Then %Do ;
  %Let Pattern = %SysFunc( PRXParse( &PRX ) ) ;
%End ;
%Else %Do ;
  %Let Pattern = %SysFunc( TranWrd( &Pattern , * , .* ) ) ;
  %Let Pattern = %SysFunc( PRXParse( /^&Pattern$/i ) ) ; 
%End ;

%Do I = 1 %To &VarNum ;
  %Let VarName = %Sysfunc( VarName( &DSID , &I ) ) ;

  %If ( %SysFunc( PRXMatch( &Pattern , &VarName ) ) > 0 ) %Then %Do ;
    %Let VarList = &VarList &VarName ; 
  %End ;
%End ;

%Let Close = %SysFunc( Close( &DSID ) ) ;


%If ( %Length( &VarList ) EQ 0 ) %Then %Do ;
  %Put ;
  %Put ;
  %Put NOTE: Could Not Find A Variable That Matched Pattern. ;
  %Put ;
  %Put ;
%End ;

&VarList 

%Mend VarMatch ;

Example:

Data Have ;
ABDC = 1 ;
ABCD = 2 ;
XYZC = 3 ;
ABFC = 4 ;
Run ;

Title "Print Out For Vars Ending With C" ;
Proc Print
 Data = Have ;
 Var %VarMatch( DSN = Have , Pattern = *C ) ;
Run ;


Output:
Print Out For Vars Ending With C                                
  Obs    ABDC    XYZC    ABFC
   1      1       3       4

Macro In Function

In the Data Step world there is the In function. Up until recently there was for this ever so helpful little function. The SAS tried in version 9 to create one at the users request #In. However, since the # was a free character for so long that many programmers used this for all sorts of things like a separator for Macro list. Not too suprisingly it made many older programs bomb and well that breaks a SAS golden rule of always making code backwards compatible. So they disabled this feature, but Tech support was still getting calls wanting such a feature. Tech Support has this for there code:

%macro in()/parmbuff; 
%local i; 
%let parms=%qsubstr(&syspbuff,2,%length(&syspbuff)-2); 
%let var=%scan(&parms,1,%str(,)); 
%let numparms=%eval(%length(&parms)- 
              %length(%sysfunc(compress(&parms,%str(,))))); 
%let infunc=&var eq %scan(&parms,2,%str(,)); 

%do i= 3 %to (&numparms + 1); 
  %let thisparm=%scan(&parms,&i,%str(,)); 
  %let infunc=&infunc or &var eq &thisparm; 
%end; 
(&infunc) 
%mend; 

All I can say is Yuck!!!! who walked in and spewed that hunk of crap!!!!

So I wrote my own which is simplier and actially has some error handling.

%Macro IsIn( LookFor= , List= , Case=I ) ;
/***************************************************/
/** Name       : IsIn                             **/
/**                                               **/
/** Parameters : LookFor ~ Text To Search For In  **/
/**                        Target String (List).  **/
/**                                               **/
/**              List    ~ Target Sting To Search **/
/**                                               **/
/**              Case    ~ Determines Whether The **/
/**                        Search Is Case         **/
/**                        Sensitive              **/
/**                        I = Case Insensitive   **/
/**                        Blank = Case Sensitive **/
/**                                               **/
/** Purpose    : The Macro Version Of The In      **/
/**              Function. Returns 1 If It Can    **/
/**              Find The Text And 0 If It Cant   **/
/***************************************************/
%Local Pattern ;


%If ( %Length( &LookFor ) = 0 ) %Then %Do ;
  %Put ;
  %Put ERROR: Please Specify A Valid Value For The LookFor Parameter!!! ;
  %Put ;
  %Return ;
%End ;

%If ( %Length( &List ) = 0 ) %Then %Do ;
  %Put ;
  %Put ERROR: Please Specify A Valid List Of Values For The List Parameter!!! ;
  %Put ;
  %Return ;
%End ;

%If ( ( %Length( &Case ) Ne 0 ) And ( &Case NE I ) And
      ( &Case NE i ) ) %Then %Do ;
   %Let Case = I ;
   %Put ;
   %Put WARNING: The Value For Parameter Case Is Not Valid. ;
   %Put WARNING: A Value Of I Will Be Used Instead. ;
   %Put ;
%End ;

%Let Pattern = %SysFunc( PRXParse( /(?=&LookFor)/&Case ) ) ;

%Eval( %SysFunc( PRXMatch( &Pattern , &List ) ) > 0 ) ;

%Mend IsIn ;

Breaking A Data Set Into N Number Of Data Sets

/***************************/
/** Create Some Data      **/
/***************************/
  Proc Summary
   Data = SASHELP.prdsal3 NWay ;
   Class Country State ;
   Output Out = Sums ( Drop = _Freq_ _Type_ ) Sum( Actual ) = Sales ;
  Run ;


/********************************************/
/** Because The Hash Method Shown Below    **/
/** Requires An Explicit List Of Variables **/
/** To Be Included In The OutPut Data Sets **/
/** This Macro Variable Is A Sort Cut So   **/
/** That They Do Not Have To Be ard Coded. **/
/********************************************/
  Proc SQL NoPrint ;
  Select Quote(Strip(Name)) Into : VarNames Separated by ' , '
   From Dictionary.Columns 
    Where LibName = 'WORK' 
      And MemName = 'SUMS' ;
  Quit ;



  Data _Null_ ;
  If 0 Then Set Sums ;

  DCL Hash Countrys  ( Ordered: 'A'    ) ;
  Countrys.definekey  ( 'Country' , '_N_' ) ;
  Countrys.definedata ( &VarNames ) ;
  Countrys.definedone () ;

  Do _N_ = 1 By 1 Until ( Last.Country ) ;
    Set Sums ;
    By Country ;
    Countrys.Add() ;
  End ; 

  Temp = Compress( Country , '.' ) ;
  Countrys.Output ( DataSet: Temp ) ;

Run ;

Part 1 of 3

This is Part 1 of a 3 part series on advanced Macro programming. Most programmers who use Macros only think of a Macro as a means to reproduce Data Step or Procedure code with minor tweaks. This mind set for beginning programmers is a good thing, however as a programmers ability increases there arises a need to have a macro read a Data Set, read a external file, or write to an external file entirely in pure Macro code.

Example 1:

  %Macro GetValues( DataIn = , Variable = ) ;
  %Local DSID GetNextObs GetVarNum Close I ;
  
  %Let DSID = %SysFunc( Open( &DataIn , I ) ) ;
  
  %Do I = 1 %To %SysFunc( Attrn( &DSID , NLOBS ) ) ;
    %Let GetNextObs = %SysFunc( FetchObs( &DSID , &I ) ) ;
    %Let GetVarNum  = %SysFunc( VarNum( &DSID , &Variable ) ) ;
  
    %SysFunc( GetVarC( &DSID , &GetVarNum ) )  
  %End ;
  
  %Let Close = %SysFunc( Close( &DSID ) ) ;
  
  %Mend GetValues ;

  %Put %GetValues( DataIn = SASHELP.CLASS , Variable = Name ) ;

There are a few things that one needs to know to read a data set with pure macro code. First is how to open and close a Data Set. For this we use the Open and Close functions. Since these are not Macro functions we need away to get the Macro facility to interface with them, so we see %Sysfunc which allows us to do just that. It is important to note that you cannot forget to Close your Data Set. By default all Data Sets are closed at the end of a Data Step, however you aren’t in that environment so it is left to, you, the programmer to close the Data Set. If you do forget to Close your Data Set you will not be able to use the Data Set again until Close it or restart your SAS session. This is due to the fact that when you open a Data Set in this manner you lock it down until you specifically release it for further use.

Next we use the Attrn function to retrieve the number of observations in the Data Set. In a normal Data Step there is an implicit loop which cycles through the observations for you. Since we don’t have that implicit loop we need to construct our own loop and thus need specify how many times we want to loop through the Data Set.

In a Data Step we have the Program Data Vector which is were the data is brought into and manipulated while the Data Step is running before writing the data out. We also need such a space and to do this we use FetchObs which gets the observation specified in the second argument and brings its value(s) into what is called the Data Set Data Vector.

Now that we have an observations value in a place we can retrieve it we need to specify which variable’s value we want to retrieve. For this we use the VarNum function, which when passed the Data Set Id and variable name will return the variables position in the Data Set.

Finally we can retrieve the variables value with GetVarC which gets character variables values from a Data Set and returns said value to a Macro variable. If one wanted to retrieve a numeric variables value then use GetVarN.

Last but not least after all observations are processed we close the Data Set with the Close function.

In our next installment we will see how to read all the files in a directory with pure Macro code.

Part 2 of 3 "A Macro That Reads All Files In A Directory"

   %Macro GetFiles( FilePath= ) ;
   %Local MyFile FileName DSID I Close ;
   
   %Let MyFile   = MyDir ;
   %Let FileName = %SysFunc( FileName( MyFile , &FilePath ) ) ;
   %Let DSID     = %SysFunc( DOpen( &MyFile ) ) ; 
      
   %Do I = 1 %To %SysFunc( DNum( &DSID ) ) ;
     %SysFunc( DRead( &DSID , &I ) )  
   %End ;
   
   %Let Close = %SysFunc( DClose( &DSID ) ) ;
   %Mend GetFiles ;

We need to allocate the directory to a file statement. To do this and keep it all in pure macro code, we use the FileName function. Like the last macro, we need to open the directory so that we can read all the file names. In the last macro, we used the Open function which works on SAS datasets, not directories. So SAS has the DOpen to open a directory. We need to count the number of files in the directory and for that we have Dnum, which returns the number of files in the directory. Next we use the DRead function to read a file name. Finally, we use the DClose to close the directory.

Next we will see how to have a macro write data to an external file.

Part 3 of 3 "A Macro That Creates And Writes Data To A Text File"

    %Macro Write2File( FileOut= ) ;
    %Local File FileName FileId I Text Write Close ;

    %Let File     = MyFile ;

    %Let FileName = %SysFunc( FileName( File , &FileOut ) ) ;

    %Let FileId   = %SysFunc( FOpen( &File , O , , P    ) ) ;

    %Do I = 1 %To 50 ;
      %Let Text   = %SysFunc( FPut( &FileId , &I        ) ) ;
      %Let Write  = %SysFunc( FWrite( &FileId           ) ) ;
    %End ;

    %Let Close    = %SysFunc( FClose( &FileId           ) ) ;

    %Mend Write2File ;

%Write2File( FileOut = C:\Documents and Settings\Toby Dunn\Desktop\ABC.TXT ) 

The Write2File Macro is a simple example of how to have a macro create and write text to a file.

%Let File = MyFile ; Creates a alais for the file, it is just like the alias one would use in a normal FileName statement in any SAS program.

%Let FileName = %SysFunc( FileName( File , &FileOut ) ) ; Alocates a FileName Statement with the alias in the macro variable File. The FileOut macro variable contains the path and the name of the output file with its extension.

%Let FileId = %SysFunc( FOpen( &File , O , , P ) ) ; Opens the file so that the values can be written to it. Incedently it is also step where the file gets physically created.

%Let Text = %SysFunc( FPut( &FileId , &I ) ) ; Creates the text to be written.

%Let Write = %SysFunc( FWrite( &FileId ) ) ; Physically writes the text in the macro variable Text to the created file.

%Let Close = %SysFunc( FClose( &FileId ) ) ; Closes the created file.

Welcome to my little space on the web. After many people asking, prodding, poking, yelling, begging in some cases, and even a few people threatened things which sounded like something straight out of the Spanish inquisition I have decided I would cave in and start writing all of my crazy and hopefully not so crazy thoughts about SAS and SAS Programming. I can think of no better place than the sasCommunity Wiki to do it on. One of the great things about SAS is its users and this greatness is exemplified by the folks who have creates this Wiki. So a big thanks to all those great peeps out there that have made this great one stop shop for all things SAS possible.