topic Re: Collapsing rows and summing one variable in SAS Programming

Collapsing rows and summing one variable

righcoastmike — Fri, 30 Nov 2018 03:40:59 GMT

Hi All,

First off if this has been answered I apologize, I've been looking around the forums and I've found a bunch of answers that are close, but don't quite work for what I'm looking for. Here's what I've got

data have;
input studyID FSA Exposure ED_binary ;
cards;
1 B2Y 384 1
1 B2Y 384 0
1 B2y 384 1
2 BgT 1000 0
3 M6D 400 1
3 M6D 400 1 
3 M6D 400 1

run;

Essentially what I am looking to do is get one record per person. I need to collapse studyid FSA and Exposure, and then sum the ed _binary variable. so the resuting dataset would look like :

data want;
input studyID FSA Exposure ED_binary ;
cards;
1 B2Y 384 2
2 BgT 1000 0
3 M6D 400 3


run;

the closest i've come is with proc SQL but that doesn't give me a dataset, just the report. I've tried proc means as well but my system just keeps hanging (I have a fairly large dataset).

as always any thoughts would be much appreciated.

Thank so much

Rightcoast

Re: Collapsing rows and summing one variable

novinosrin — Fri, 30 Nov 2018 03:47:35 GMT

data have;
infile cards truncover;
input studyID  FSA $ Exposure ED_binary ;
cards;
1 B2Y 384 1
1 B2Y 384 0
1 B2Y 384 1
2 BgT 1000 0
3 M6D 400 1
3 M6D 400 1 
3 M6D 400 1
;
run;


proc sql;
create table want as
select studyid, fsa, exposure, sum(ed_binary) as ed_binary
from have
group by studyid, fsa, exposure;
quit;

Re: Collapsing rows and summing one variable

mkeintz — Fri, 30 Nov 2018 06:22:48 GMT

data have;
input studyID FSA $3. Exposure ED_binary ;
cards;
1 B2Y 384 1
1 B2Y 384 0
1 B2Y 384 1    was B2y
2 BgT 1000 0
3 M6D 400 1
3 M6D 400 1 
3 M6D 400 1
run;
data want (drop=cumsum);
  set have;
  by studyid fsa exposure;
  cumsum+ed_binary;
  if not first.exposure then  ed_binary=cumsum;
  else cumsum=ed_binary;
  if last.exposure;
run;

This assumes data are sorted by studyid fsa exposure. Note I corrected the value of FSA in the 3rd record.

Re: Collapsing rows and summing one variable

righcoastmike — Fri, 30 Nov 2018 15:04:43 GMT

Thanks so much, this one worked great as well!

Much appreciated.

Mike

Re: Collapsing rows and summing one variable

righcoastmike — Fri, 30 Nov 2018 15:05:48 GMT

This did the trick perfectly, and actually helped me to locate another little quirk in my dataset that needs to be fixed, so you were even more helpful than you thought!

Thanks so much

Mike