Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Sunday, November 13, 2011

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

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

/*
原作者问题:
今天遇到一个很简单的case,用datastep能够解决,不过感觉太麻烦了,我觉得SQL应该能很方便的解决这个问题,而平时自己sql又不熟悉,只好上版上来找好心人问问了。问题很简单,我有两个一摸一样的dataset,pre和post,我要对20多个variable做post-pre,如果用datastep的话,每个dataset里面的variable都要改名加上后缀pre,post,然后要再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; 

Wednesday, May 11, 2011

SUM within subgroup

/* Within the subgroup of ID, if seq is the same, then calculate the sum of unit for the same seq;*/

/* Else, if seq is different then keep the value of unit */

options formdlim='-';

data test;

input ID seq unit;

cards;

100 1 10

100 2 10

101 1 10

101 1 20

103 1 10

103 2 10

104 1 10

104 1 20

;

run;

/* First is to use DATA STEP */

proc sort data=test;

by id seq;

run;

data want (keep=id seq sumunit);

set test;

by id seq;

if first.id or first.seq then sumunit=unit;

else sumunit+unit;

if last.id or last.seq then output;

run;

proc print data=want;

run;

/* Second is PROC SQL with GROUP BY */

proc sql;

select id, seq, sum(unit) as s

from test

group by id, seq

order by id, seq;

run;

quit;

Thursday, February 10, 2011

data set match merge, an example to study zz

The original post want to match merge A and B, and then got the result as below:

X   Y         Z               flag
1   red      brown       0
1   red      red            1
1   blue     brown       0
1   blue     red            0
3   yellow  red            0
3   yellow  pink           0
3   green   red            0
3   green   pink           0

In proc sql, it's not difficult to use inner join to merge A and B conditional on a.x = b.x. And we need to assign value to flag as if a.y matched by b.z.  At last the program can be down as:



data a;
 input x y $;
 cards;
 1 red
 1 blue
 3 yellow
 3 green
 ;
run;

data b;
 input x z $;
 cards;
 1 brown
 1 red
 2 yellow
 3 red
 3 pink
 ;
run;

proc sql;
   select a.x, a.y, b.z, case when a.y = b.z then 1 else 0 end as flag 
   from a, b
   where a.x=b.x;
quit;





***************************************************************************
 Somebody gives the solution with data step. It's pretty good, as following:



 data c;
 set a;
 do i = 1 to num;
  set b(rename = (x = x2)) nobs = num point = i;
  output;
 end;
run;

data want (drop = x2);
 set c;
 if y = z then flag = 1;
 else flag = 0;
 where x = x2;
run;


To make it clearer, here, the print of data set c is:

                                 Obs    x    y         x2    z

                                    1    1    red        1    brown
                                    2    1    red        1    red
                                    3    1    red        2    yellow
                                    4    1    red        3    red
                                    5    1    red        3    pink
                                    6    1    blue       1    brown
                                    7    1    blue       1    red
                                    8    1    blue       2    yellow
                                    9    1    blue       3    red
                                   10    1    blue       3    pink
                                   11    3    yellow     1    brown
                                   12    3    yellow     1    red
                                   13    3    yellow     2    yellow
                                   14    3    yellow     3    red
                                   15    3    yellow     3    pink
                                   16    3    green      1    brown
                                   17    3    green      1    red
                                   18    3    green      2    yellow
                                   19    3    green      3    red
                                   20    3    green      3    pink

Thursday, January 13, 2011

Creating cummulative sum using DATA STEP and PROC SQL


options formdlim='-' nocenter nodate;

/*********************************************************
*Here is the original data ;                             *
*We want to calculate the cummulative sum for each famid;*
*********************************************************/
data kids;
  length kidname $ 4;
  input famid kidname birth_order wt;
cards;
1 Beth 1  60
1 Barb 3  20
4 Sam  1 100
4 Stu  2  90
1 Bob  2  40
3 Pete 1  60
3 Phil 3  20
2 Andy 1  80
3 Pam  2  40
2 Al   2  50
2 Ann  3  20
;
run;

/* First we need to sort the data */
proc sort data=kids;
by famid birth_order;
run;

/* Method 1 is using DATA STEP with first. */
data new;
   set kids;
   by famid;
   retain count cum_wt;
   if first.famid then do;
      count=0;    /* Reset count to */
         cum_wt=0;   /* Reset cumsum to */
       end;
       count+1;
       cum_wt+wt;
run;


/* The second method is using PROC SQL */
proc sql;
   select
       data1.famid as famid,
          data1.birth_order as birth_order,
       data1.wt as wt,
       (
        select sum(wt)
        from kids
        where famid        = data1.famid  and
                    birth_order >= data2.min_order and
                       birth_order <= data1.birth_order
       )   as cum_wt,
          (
           select count(famid)
              from kids
              where famid        = data1.famid  and
                    birth_order >= data2.min_order and
                       birth_order <= data1.birth_order
       ) as count
   from (select * from kids) as data1,
        (select famid, min(birth_order) as min_order from kids group by famid) as data2
   where data1.famid=data2.famid;
quit;


proc print data=&syslast;
run;