Sunday, November 13, 2011
行和列一样的两个data set对应元素相减做差
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
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;
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