DO LOOP for summarizing a variable per multiple ID entries

Solved
Frequent Contributor
Posts: 136

DO LOOP for summarizing a variable per multiple ID entries

I have the following data:

``````id s1 m1 t1
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0``````

I want to create a dataset that if the value of each of the three variables s1, m1, t1 was once "1" then outpuet "1" row for that id that represent either a 0 or 1 for each id.

output:

``````id m1 s1 t1
1 1   1    0
2  1   1   1``````

Accepted Solutions
Solution
‎01-25-2017 01:26 PM
Posts: 3,852

Re: DO LOOP for summarizing a variable per multiple ID entries

Posted in reply to lillymaginta

lillymaginta wrote:

I have the following data:

``````id s1 m1 t1
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0``````

I want to create a dataset that if the value of each of the three variables s1, m1, t1 was once "1" then outpuet "1" row for that id that represent either a 0 or 1 for each id.

output:

``````id m1 s1 t1
1 1   1    0
2  1   1   1``````

It looks to me like you want the max of M1 S1 and T1 for each ID.

``````data s;
input id s1 m1 t1;
cards;
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0
;;;;
run;
proc print;
run;
proc summary data=s nway;
class id;
output out=max(drop=_:) max(s1 m1 t1)=;
run;
proc print;
run;``````

All Replies
Solution
‎01-25-2017 01:26 PM
Posts: 3,852

Re: DO LOOP for summarizing a variable per multiple ID entries

Posted in reply to lillymaginta

lillymaginta wrote:

I have the following data:

``````id s1 m1 t1
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0``````

I want to create a dataset that if the value of each of the three variables s1, m1, t1 was once "1" then outpuet "1" row for that id that represent either a 0 or 1 for each id.

output:

``````id m1 s1 t1
1 1   1    0
2  1   1   1``````

It looks to me like you want the max of M1 S1 and T1 for each ID.

``````data s;
input id s1 m1 t1;
cards;
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0
;;;;
run;
proc print;
run;
proc summary data=s nway;
class id;
output out=max(drop=_:) max(s1 m1 t1)=;
run;
proc print;
run;``````

Frequent Contributor
Posts: 136

Re: DO LOOP for summarizing a variable per multiple ID entries

Posted in reply to data_null__

An interesting smart way to think through it, that is simple! thanks!

Posts: 1,147

Re: DO LOOP for summarizing a variable per multiple ID entries

Posted in reply to lillymaginta

data have ;
input id s1 m1 t1;
cards;
1 1   0    0
1 1 1     0
1 1 0    0
2 1 0 1
2  0 1 0
;

options missing=0;
data want ;
set have(rename=(s1=s2 m1=m2 t1=t2));
by id;
retain s1 m1 t1;
if first.id then do;
s1=.;
m1=.;
t1=.;
end;
if s2 eq 1 then s1=s2;
if m2 eq 1 then m1=m2;
if t2 eq 1 then t1=t2;
if last.id;
drop s2 m2 t2;
run;

Thanks,
Jag
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 3 replies
• 197 views
• 2 likes
• 3 in conversation