"dictionary.columns"的典型运用

 

1.Create a macro variable that contains a list of all variables that exist in a SAS dataset

/* Sample data */
data one;                                                                                                                              
  input x y;                                                                                                                           
datalines;                                                                                                                             
1 2                                                                                                                                    
;

/*data步宏*/
%macro lst(dsn);                                                                                                                       
  %local dsid cnt rc;
  %global x;                                                                                                                
  %let x=;                                                                                                                             

  /* Open the data set */                                                                                                              
  %let dsid=%sysfunc(open(&dsn));                                                                                                      

  /* The variable CNT will contain the number of variables that are in the */                                                           
 /* data set that is passed in.                                           */                                                           
  %let cnt=%sysfunc(attrn(&dsid,nvars));                                                                                               

  /* Create a macro variable that contains all dataset variables */                                                                    
   %do i = 1 %to &cnt;                                                                                                                 
    %let x=&x %sysfunc(varname(&dsid,&i));                                                                                                         
   %end;                                                                                                                               
                                                                                                                                       
  /* Close the data set */                                                                                                             
  %let rc=%sysfunc(close(&dsid));                                                                                                                                        
%mend lst;                                                                                                                             
                                                                                                                                       
  /* Pass in the name of the data set */                                                                                               
%lst(one)                                                                                                                              
                                                                                                                                       
%put macro variable x = &x; 

/*proc sql宏变量，dictionary.columns*/
proc sql noprint;
     select distinct name
     into : varlist separated by ' '
     from dictionary.columns
     where libname='WORK' and memname='ONE';
quit;  

2 Dynamically rename multiple variables in a SAS data set

/* Create a sample data set.  Note the naming pattern of COL1-COL5 is */                                                 
/* incomplete.  Since COL4 is missing, a numbered range list can not   */                                                             
/* used for this rename.                                               */                                                              
                                                                                                                                       
data a;                                                                                                                                
  col1=1;                                                                                                                              
  col2=2;                                                                                                                              
  col3=3;                                                                                                                              
  col5=5;                                                                                                                              
  x=123;                                                                                                                               
run;                                                                                                                                   
                                                                                                                                       
/* Use SQL to create the macro variable VARLIST by retrieving the   */                                                                
/* variable names from DICTIONARY.COLUMNS.  Verify the LIBNAME and  */                                           

/* MEMNAME values match your existing data set.  Note the values    */                                                             
/* must be specified in upper case.  'NEW' will replace 'COL' for   */                                                                 
/* the appropriate variables.                                       */ 

proc sql noprint;                                                                                                                      
  select trim(name)||'=NEW'||substr(name,4)                                                                                            
  into :varlist separated by ' '                                                                                                       
  from DICTIONARY.COLUMNS                                                                                                              
  WHERE LIBNAME EQ "WORK" and MEMNAME EQ "A"                                                                                           
  and upcase(name) like 'COL%';                                                                                                                
quit;

/* Use PROC DATASETS to do the rename. Again, verify the libref and  */                                                          
/* member name match your data set.                                  */                                                                
                                                                                                                                       
proc datasets library=work nolist;                                                                                                     
  modify a;                                                                                                                            
  rename &varlist;                                                                                                                     
quit;                                                                                                                                  
                                                                                                                                       
/* Confirm changes with a PROC CONTENTS */                                                                                             
                                                                                                                                       
proc contents data=a;                                                                                                                  
run;

3 Placing a list of values into a series of macro variables

%macro varlist(lib,dsn);

   proc sql noprint;

  select distinct name

     into :varname1-:varname999

       from dictionary.columns

          where(libname=upcase("&lib") &

                   memname=upcase("&DSN"));

quit;

%do i=1 %to &sqlobs;
    %put &i &&varname&i;
%end;
%mend varlist

 

资料来源

http://support.sas.com/kb/25/083.html

http://support.sas.com/kb/26/009.html