请问怎样用sas aql语句在数据集任何一个位置插入一个变量

请问怎样用sas aql语句在数据集任何一个位置插入一个变量，请举实例，谢谢！

/* This is only a demo */
/* practically for a large number of variables, it is much simple to select all the variables */
/* through PROC SQL FEEDBACK */  

%macro mymacro(lib=, Old_table=, New_table=, NewVar=, col=, formular=);
proc sql noprint;
   select name into: a_var separated by ', '
      from dictionary.columns
         where  libname="%upcase(&lib)" and
                memname="%upcase(&old_table)" and
                varnum < &col;
   select name into: b_var separated by ', '
      from dictionary.columns
         where  libname="%upcase(&lib)" and
                  memname="%upcase(&old_table)" and
                  varnum ge &col;
   select max(varnum) into: nv
      from dictionary.columns
           where  libname="%upcase(&lib)" and memname="%upcase(&old_table)";
        %if &col = 1 %then %do;
            create table &new_table as
              select &formular as &newVar, &b_var
                from &lib..&old_table; %end;
        %if &col > &nv %then %do;
            create table &new_table as
              select &a_var, &formular as &newVar
                from &lib..&old_table;  %end;
        %if &col > 1 and &col <= &nv %then %do;
            create table &new_table as
              select &a_var, &formular as &newVar, &b_var
                from &lib..&old_table;  %end;
quit;
%mend mymacro;

/* example 1:  add a new variable calculating height in meter  */
%mymacro(lib=sashelp, old_table=class, new_table=class2,
         NewVar=height_in_metter, col=5, formular=height*0.0254);

data aaa;    /* create the sample dataset, before running the following code */
  retain x1-x6 (1 2 3 4 5 6);
  output; output; output;
run;

/* example 2: calculate sum of x1 and x2 and insert it as column 3 */
%mymacro(lib=work,  old_table=aaa, new_table=bbb,   
         NewVar=sum_x1x2, col=3, formular=sum(x1,x2));

/* example 3: calculate sum of all variables and insert it as the last column */
%mymacro(lib=work,  old_table=aaa, new_table=ccc,
         NewVar=sum_all, col=7, formular=sum(x1,x2,x3,x4,x5, x6));

/* example 4: calculate sum of variable 5  and put as the 6th column */
%mymacro(lib=work,  old_table=aaa, new_table=ddd,
         NewVar=x5_total, col=6, formular=sum(x5));

/* example 5: insert a character at column 1 of the new table */
%mymacro(lib=work,  old_table=aaa, new_table=eee,
         NewVar=Group, col=1, formular='A');

proc print data=class2; title 'class2'; run;
proc print data=bbb;    title 'bbb'; run;
proc print data=ccc;    title 'ccc'; run;
proc print data=ddd;    title 'ddd'; run;
proc print data=eee;    title 'eee'; run;

See title.

data t1;
  retain x1-x5 (1 2 3 4 5);
  output;
  run;
title "insert a variable at the begining position";
proc sql;
  select sum(x1,x2) as y, *
  from t1;
  quit;

title "insert a variable at the last position";
proc sql;
  select *, sum(x1,x2) as y
  from t1;
  quit;

title "insert a variable at the position you specified";
proc sql;
  select x1,x2, sum(x1,x2) as y, x3,x4,x5
  from t1;
  quit;

1. %let position=3;
   
2. %let dataset = sashelp.class;
   
3. %let newdataset = xxx;
   
4. %let newvar = weight*703/height**2 as bmi;
   
5. proc sql noprint;
   
6. %global vars_before vars_after;
   
7. *vars_before = variable list before poistion 3;
   
8. select name into :vars_before separated by ','
   
9.         from dictionary.columns
   
10.         where upcase(libname) = coalesec(upcase(scan("&dataset", -2, '.')), 'WORK')
    
11.                 and upcase(memname)=upcase(scan("&dataset",-1,'.'))
    
12.                 and varnum<&position
    
13.         order by varnum;
    
14. *vars_after = variable list after poistion 2;
    
15. select name into :vars_after separated by ','
    
16.         from dictionary.columns
    
17.         where upcase(libname) = coalesec(upcase(scan("&dataset", -2, '.')), 'WORK')
    
18.                 and upcase(memname)=upcase(scan("&dataset",-1,'.'))
    
19.                 and varnum>=&position
    
20.         order by varnum;
    
21. create table xxx as
    
22.         select %sysfunc(catx(%str(,), &vars_before, &newvar, &vars_after))
    
23.         from &dataset;
    
24. quit;

复制代码

yongyitian 发表于 2013-5-13 23:45
/* This is only a demo */
/* practically for a large number of variables, it is much simple to sel ...

好东西，我收藏了

playmore 发表于 2013-5-14 08:30
好东西，我收藏了

大侠的macro写的非常专业，一定要仔细拜读。

谢谢各位高手！！