Sunday, November 13, 2011

行和列一样的两个data set对应元素相减做差

mitbbs上看到的一个问题,两个data set (pre, post),行和列都一样,要做的是对两个data set对应的值做差。

/*
原作者问题:
今天遇到一个很简单的case,用datastep能够解决,不过感觉太麻烦了,我觉得SQL该能很方便的解决这个问题,而平时自己sql又不熟悉,只好上版上来找好心人问问了。问题很简单,我有两个一摸一样的datasetprepost,我要对20多个variablepost-pre,如果用datastep的话,每个dataset里面的variable都要改名加上后缀prepost,然后要再merge到一个大的dataset里面,然后还要计算diff,感觉操作起来非常复杂。我想做得是用第一个post dataset 整个减掉 pre dataset 里面每个variable 对应
的值,请问要怎么操作呢? 我来编个dataset吧!假设

pre

id   measure1 measure2 measure3
1       20               30             40
2       50               60             70

post

id   measure1 measure2 measure3
1       15               20             10
2       20               40             45

我想得到如下的dataset
diff
id   measure1 measure2 measure3
1        5                10             30
2       30               20             25

*/



/*  俺给的解答  */
data pre;
input id   measure1 measure2 measure3;
cards;
1       20               30             40
2       50               60             70
;

data post;
input id   measure1 measure2 measure3 ;
cards;
1       15               20             10
2       20               40             45
;
run;

%macro selectdif(maxindex);
      %do i=1 %to &maxindex;
            pre.measure&i-post.measure&i as dif&i
            %if &i ne &maxindex %then ,
            ;
      %end;
%mend;

proc sql;
      select pre.id, %selectdif(maxindex=3)
      from pre, post
      where pre.id=post.id;
quit; 

2 comments:

  1. I don't understand this part:%if &i ne &maxindex %then ,

    can you explain more?

    thanks

    ReplyDelete
  2. your sql should be: select pre.id, per.measure1-post.meanure1 as dif1, per.measure2-post.meanure2 as dif2, per.measure3-post.meanure3 as dif3

    To change this to macro, for 1 and 2, you need add a ',', after '3' there is no ',', then < %if &i ne &maxindex %then , > is to do this.

    ReplyDelete