SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star
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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20
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;
righcoastmike
Quartz | Level 8

Thanks so much, this one worked great as well! 

 

Much appreciated. 

 

Mike 

 

 

mkeintz
PROC Star
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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
righcoastmike
Quartz | Level 8

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 

 

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 3199 views
  • 0 likes
  • 3 in conversation