DATA Step, Macro, Functions and more

Retain Multiple Variables

Reply
Regular Contributor
Posts: 183

Retain Multiple Variables

[ Edited ]

I want to retain all the variables. Please see the raw data and desired data below -

 

img1.png

 

data temp;
input ID x y z;
cards;
1 25 . .
1 . 35 .
1 . . 40
2 30 . .
2 . 25 .
3 50 . .
;
run;

data temp2;
set temp;
by ID;
retain x1 0;
if first.ID then X1=x;
else X1=X1;
run;

 

I have done it for variable x but i am unable to do it for other variables.

 

Respected Advisor
Posts: 4,919

Re: Retain Multiple Variables

[ Edited ]

A double DO UNTIL() can perform this operation:

 

data temp;
input ID x y z;
cards;
1 25 . .
1 . 35 .
1 . . 40
2 30 . .
2 . 25 .
3 50 . .
;

data want;
array _a{3} x y z;
array _b{3};
do until(last.ID);
    set temp; by ID;
    do i = 1 to dim(_a);
        _b{i} = coalesce(_b{i}, _a{i});
        end;
    end;
do until(last.ID);
    set temp; by ID;
    do i = 1 to dim(_a);
        _a{i} = _b{i};
        end;
    output;
    end;
drop i _b:;
run;

proc print; run;
PG
Super User
Posts: 10,020

Re: Retain Multiple Variables

data temp;
input ID x y z;
cards;
1 25 . .
1 . 35 .
1 . . 40
2 30 . .
2 . 25 .
3 50 . .
;
run;
proc means data=temp noprint;
by id;
var x y z ;
output out=temp1 sum=;
run;
data want;
 set temp1(drop=_type_);
 do i=1 to _freq_;
  output;
 end;
 drop i _freq_;
run;

Ask a Question
Discussion stats
  • 2 replies
  • 261 views
  • 2 likes
  • 3 in conversation