求助高手:如何根据配制表,来抽取数据,组成宽表?

shiyiming

根据配制表,来抽取数据,组成宽表.比如现在有三个原始表class1,class2,class3和一个配制表confect.现在根据配制表confect的内容,在三个原始表里取相应的字段,结成宽表taball;数据如下:
data class1(rename=(name=name1 sex=sex1 age=age1 height=height1 weight=weight1));
set sashelp.class;
run;
data class2(rename=(name=name2 sex=sex2 age=age2 height=height2 weight=weight2));
set sashelp.class;
run;
data class3(rename=(name=name3 sex=sex3 age=age3 height=height3 weight=weight3));
set sashelp.class;
run;
data confect;
input tabName $ colName $ ;
     DATALINES;
         class1  name1
         class1  age1
         class2  name2
         class2  age2
         class3  name3
         class3  age3
run;
最后的宽表taball的列有class1.name1,class1.age1,class2.name2,class2.age2,class3.name3,class3.age3
请求高手给点指导,谢谢!

shiyiming

data class1(rename=(sex=sex1 age=age1 height=height1 weight=weight1));
set sashelp.class;
run;
data class2(rename=(sex=sex2 age=age2 height=height2 weight=weight2));
set sashelp.class;
run;
data class3(rename=(sex=sex3 age=age3 height=height3 weight=weight3));
set sashelp.class;
run;
data confect;
input storeName $ tabName $ colName $ ;
     DATALINES;
         sashelp  class  name
         sashelp  class  age
         work          class1 age1
         work          class1 sex1
         work     class2  sex2
         work     class2  age2
         work     class3  height3
         work     class3  age3
         work     class3  weight3
run;
options mprint mlogic;
%macro meageTable(tab,tid);
%let dsid=%sysfunc(open(&tab,i));
%let Totnum=%sysfunc(attrn(&dsid,NOBS));
%let lcname=  twhere=  stname=  storetable= ;
        %do seq= 1 %to  %eval(&Totnum);
                %let rc=%sysfunc(fetchobs(&dsid,&seq));
                %let store=%sysfunc(getvarc(&dsid,1));
                %let tname=%sysfunc(getvarc(&dsid,2));
        %let cname=%sysfunc(getvarc(&dsid,3));
                data _null_;
                        call symput('storetable',"&store"||"."||"&tname"||" "||"&store"||"&tname");
                run;
                %if &seq=1 %then %do;
                        data _null_;
                                call symput('stname',"&storetable");
                                call symput('lcname',"&tname"||"."||"&cname");
                                call symput('twhere',"&store"||"&tname"||"."||"&tid");
                        run;
                %end;
                %else %do;
                        %let a=%index("&stname",&storetable);
                        %if &a>0 %then %do;
                                data _null_;
                                        call symput('lcname',"&lcname" ||","||"&tname"||"."||"&cname");
                                run;
                        %end;
                        %else %do;
                                data _null_;
                                        call symput('stname',"&stname" || "," || "&storetable");
                                        call symput('lcname',"&lcname" ||","||"&tname"||"."||"&cname");
                                        call symput('twhere',"&twhere"||"="||"&store"||"&tname"||"."||"&tid");
                                run;
                        %end;
                        %put "lcname============================" &lcname;
                        %put "stname============================" &stname;
                        %put "twhere============================" &twhere;
                %end;
        %end;
        proc sql;
                create table classall as
                        select &lcname from &stname
                        where &twhere;
        quit;
%mend meageTable;
%meageTable(work.confect,name);

这是我写的代码,功能是实现了,但觉得代码不够简化,大家指教一下,那些地方可以优化的?

shiyiming

<!-- s:shock: --><img src="{SMILIES_PATH}/icon_eek.gif" alt=":shock:" title="Shocked" /><!-- s:shock: -->
            为什么我的问题只有我自己在唱独角戏呢?是不是我表达的不够明确或是表达能力有问题?

shiyiming

太长了，我看看先

shiyiming

我很喜欢“宽表”这种白痴一样通俗的新词，所以回你一贴。
[code:2ez6j1ml]data _null_;
  set confect end=end;
  file 'c&#58;\temp&#46;txt';
  if _n_=1 then put 'data taball; merge ';
  length commmand $200;
  command=compress(tabname||'(keep='||colname||')');
  put command;
  if end then put '; run;';

run;

%include 'c&#58;\temp&#46;txt';[/code:2ez6j1ml]

shiyiming

楼上的代码太精点了,多谢!!!

shiyiming

[color=#BF0000:5wklhnxs]to ahuige [/color:5wklhnxs]
用merge做表合并的话,如果要用到by name,那compress好像不行啊.

shiyiming

[quote=&quot;ydlin&quot;:d73xwkil][color=#BF0000:d73xwkil]to ahuige [/color:d73xwkil]
用merge做表合并的话,如果要用到by name,那compress好像不行啊.[/quote:d73xwkil]

没用的家伙，出去不要说我回过你的贴

shiyiming

回楼上的大哥:
不是我没用啊,
data taball;
merge
sashelp.class(keep=name)
sashelp.class(keep=age)
work.class1(keep=sex1)
work.class2(keep=age2)
work.class3(keep=weight3)
;
by name;
run;
这样的语句是错误的,如果要用by name做条件合并的话,每个keep里都要有name的啊.

shiyiming

[code:2wgab1id]data _null_;
  set confect end=end;
  file 'c&#58;\temp&#46;txt';
  if _n_=1 then put 'data taball; merge ';
  length commmand $200;
  command=tabname||'(keep=name '||colname||')';
  put command;
  if end then put ';by name; run;';

run;

%include 'c&#58;\temp&#46;txt';[/code:2wgab1id]

朽木不可雕也