SAS中文论坛

标题: 求助高手:如何根据配制表,来抽取数据,组成宽表? [打印本页]

作者: shiyiming 时间: 2008-9-11 10:52
标题: 求助高手:如何根据配制表,来抽取数据,组成宽表?
根据配制表,来抽取数据,组成宽表.比如现在有三个原始表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 时间: 2008-9-16 11:20
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
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 时间: 2008-9-24 17:32
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
<img src="{SMILIES_PATH}/icon_eek.gif" alt=":shock:" title="Shocked" />
为什么我的问题只有我自己在唱独角戏呢?是不是我表达的不够明确或是表达能力有问题?

作者: shiyiming 时间: 2008-9-26 11:29
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
太长了，我看看先

作者: shiyiming 时间: 2008-9-26 14:16
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
我很喜欢“宽表”这种白痴一样通俗的新词，所以回你一贴。
[code:2ez6j1ml]data _null_;
  set confect end=end;
  file 'c:\temp.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:\temp.txt';[/code:2ez6j1ml]

作者: shiyiming 时间: 2008-9-26 16:29
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
楼上的代码太精点了,多谢!!!

作者: shiyiming 时间: 2008-9-27 11:56
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
[color=#BF0000:5wklhnxs]to ahuige [/color:5wklhnxs]
用merge做表合并的话,如果要用到by name,那compress好像不行啊.

作者: shiyiming 时间: 2008-9-27 13:06
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
[quote="ydlin":d73xwkil][color=#BF0000:d73xwkil]to ahuige [/color:d73xwkil]
用merge做表合并的话,如果要用到by name,那compress好像不行啊.[/quote:d73xwkil]

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

作者: shiyiming 时间: 2008-9-27 13:16
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
回楼上的大哥:
不是我没用啊,
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 时间: 2008-9-27 13:35
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
[code:2wgab1id]data _null_;
  set confect end=end;
  file 'c:\temp.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:\temp.txt';[/code:2wgab1id]

朽木不可雕也

作者: shiyiming 时间: 2008-9-27 13:46
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
大哥说得好, <img src="{SMILIES_PATH}/icon_sad.gif" alt=":(" title="Sad" /> 多谢教诲.

作者: shiyiming 时间: 2008-9-27 14:34
标题: Re: 求助高手:如何根据配制表,来抽取数据,组成宽表?
[code:2zts6zf6]
proc sql;
   create table taball as
   select name1, sex1, age1, sex2, age2, weight3, age3, height3
   from class, class1, class2, class3
   where class1.name1=class2.name2=class3.name3
   order by class1.name1;
quit;
[/code:2zts6zf6]

欢迎光临 SAS中文论坛 (https://mysas.net/forum/)