SQL應用

問題：要一次把整個 dataset variables 都 rename
說明：
ref: Renaming All Variables in a SAS Data Set Using the Information from PROC SQL's Dictionary Tables

options macrogen mprint mlogic ;
%macro rename(lib,dsn);
  options pageno=1 nodate ;
  proc contents data=&lib..&dsn;
    title "Before Renaming All Variables";
  run;
  proc sql noprint;
    select nvar into :num_vars
    from dictionary.tables
    where libname="&LIB" and memname="&DSN";
    select distinct(name) into :var1-:var%TRIM(%LEFT(&num_vars))
    from dictionary.columns where libname="&LIB" and memname="&DSN";
  quit;

  proc datasets library=&LIB;
    modify  &DSN;
    rename
      %do i=1 %to &num_vars;
      &&var&i=NEWNAME_&&var&i.  %end;
     ;
  quit;
  options pageno=1 nodate;
  proc contents data=&lib..&dsn;
    title "After Renaming All Variables";
  run;
%mend rename;
%rename(WORK,ONE);

以上的用法有2個重點

• 應用 sas 系統 library SASHELP 的 dictionary tables
• sql 的 into: 可產生 macro variable
  適用只有1列資料
  例如 select NAME into: source_err from dataset
  若有n列 NAME 希望得到 &source_err 為 n1,n2,n3 就要下 separated by
  例如 select NAME into: source_err separated by ',' from dataset
  若有n列 NAME 希望得到 n 個 macro variable 就如下
  select distinct(name) into :var1-:varN

另外，能用 dictionary tables 的話也可以直接套 call execute() 去改 data set

---