DATA Step, Macro, Functions and more

DO LOOP for summarizing a variable per multiple ID entries

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

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
Respected Advisor
Posts: 3,777

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


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;

Capture.PNG

 

 

View solution in original post


All Replies
Solution
‎01-25-2017 01:26 PM
Respected Advisor
Posts: 3,777

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


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;

Capture.PNG

 

 

Frequent Contributor
Posts: 110

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

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

Trusted Advisor
Posts: 1,128

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

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
  • 137 views
  • 2 likes
  • 3 in conversation