topic Re: How to do row calculation by group in SAS Programming

How to do row calculation by group

stataq — Tue, 26 Mar 2024 18:37:43 GMT

Hello

I want to do row calculation with the following rule:

The calculation is done per ID.

IF Y(Y= score, std) ) in time 1 is null and Y in time 2 and 3 are not null then Y in time 1 will be equal to Y in time 2* time 3.

Otherwise Y remain as is.

What is the way to do it please?

Data have;
input ID time score std;
cards;
111 1 . .
111 2 . .
111 3 7 5
222 1 4 6
222 2 5 7
222 3 4 8
333 1 . .
333 2 6 2
333 3 7 4
444 1 . .
444 2 6 4
444 3 7 2
;
Run;

expected output will looks like below:

ID	time	score	std
111	1	.	.
111	2	.	.
111	3	7	5
222	1	4	6
222	2	5	7
222	3	4	8
333	1	42	8
333	2	6	2
333	3	7	4
444	1	42	8
444	2	6	4
444	3	7	2

Re: How to do row calculation by group

Tom — Tue, 26 Mar 2024 19:15:39 GMT

Data is not structed properly for that type of calculation, but that is easy to fix with PROC TRANSPOSE.

data have;
  input ID time score std;
cards;
111 1 . .
111 2 . .
111 3 7 5
222 1 4 6
222 2 5 7
222 3 4 8
333 1 . .
333 2 6 2
333 3 7 4
444 1 . .
444 2 6 4
444 3 7 2
;

proc transpose data=have out=haveT name=MEASURE prefix=Y;
  by id;
  id time;
  var score std;
run;

proc print;
run;

data want;
  set havet ;
  y1 = coalesce(y1,y2*y3);
run;

proc print;
run;

Results:

Re: How to do row calculation by group

stataq — Tue, 26 Mar 2024 19:59:40 GMT

How can I change the data back to old arrangement?

Re: How to do row calculation by group

mkeintz — Wed, 27 Mar 2024 01:11:11 GMT

If you want to preserve the original structure then you have to pass through each ID group twice, the first time to store the SCORE and STD for time 2 and time 3, the second pass to calculate a new SCORE and STD for time 1 if appropriate. The data are output during the second pass:

data have;
  input ID time score std;
cards;
111 1 . .
111 2 . .
111 3 7 5
222 1 4 6
222 2 5 7
222 3 4 8
333 1 . .
333 2 6 2
333 3 7 4
444 1 . .
444 2 6 4
444 3 7 2
run;

data want ;
  set have (in=firstpass)  have (in=secondpass);
  by id;
  array sc {2:3} _temporary_;
  array st {2:3} _temporary_;

  if first.id then call missing (of sc{*}, of st{*});
  if firstpass=1 and (2<=time<=3) then do;
    sc{time}=score;
    st{time}=std;
  end;

  if secondpass;
  if time=1 then do;
    if missing(score) and nmiss(of sc{*})=0 then score=sc{2}*sc{3};  
    if missing(std)   and nmiss(of st{*})=0 then std=st{2}*st{3};  
  end;
run;

Re: How to do row calculation by group

Ksharp — Wed, 27 Mar 2024 02:24:52 GMT

data have;
  input ID time score std;
cards;
111 1 . .
111 2 . .
111 3 7 5
222 1 4 6
222 2 5 7
222 3 4 8
333 1 . .
333 2 6 2
333 3 7 4
444 1 . .
444 2 6 4
444 3 7 2
;

data want;
 merge have 
       have(keep=ID score std rename=(id=id1 score=score1 std=std1) firstobs=2) 
       have(keep=ID score std rename=(id=id2 score=score2 std=std2) firstobs=3);
if ( id ne lag(id) and missing(score) and missing(std) )    and
   ( id eq id1 and not missing(score1) and not missing(std1) )   and
   ( id eq id2 and not missing(score2) and not missing(std2) ) then do; 
   score=score1*score2; std=std1*std2;
end;
drop id1 id2 score1 score2 std1 std2;
run;