Tom, I think PRX is the best choice. If data like the following,your code does not work !   data have; infile cards; input string &:$50.; cards; Influenza Invalid,FLU-22 Invalid Influenza Invalid(20),FLU-22 Invalid(2999) Influenza Canceled(77),FLU-22 Canceled(77) Influenza Negative,FLU-22 Positive (Influenza) ; run; data want; set have; length string2 string3 $80.; do i=1 by 2 to countw(string,'()'); string2=cats(string2,scan(string,i,'()')); end; string3 = prxchange('s/\(.*?\)//', -1, string); drop i; run; ... View more

title1 color=blue bcolor=blue ''; title2 color=blue bcolor=blue ''; title3 color=blue bcolor=blue ''; ods escapechar= '^' ; ods excel file='c:\temp\want.xlsx' options(sheet_name='class' embedded_titles='yes' hidden_rows='4'); proc report data=sashelp.class nowd style(header)={cellheight=2cm}; column ('^{style[color=red]location xxxxxx}' name sex weight height) ; define name/display style={cellwidth=2cm}; define sex/display style={cellwidth=2cm}; define weight/display style={cellwidth=1cm}; define height/display style={cellwidth=1cm}; run; ods excel close;   ... View more

Hi Joe, I checked with the DBA, and was told that limits cannot be increased randomly. Hence I optimized the query, and it’s running fine in the test Environment and hope it does the same on Prod. Thank you!! ... View more

Hi, Thank you for your reply. This is slight change in the requirement. Sorry i wasnt clear on the first time. data have ; input id (val1-val9) ($); cards; 1 abc efg hij klm npo qrs tuv wxy aaa 2 hdi iijk klm . . . . . . 3 kkk iikl . . . . . . . 4 klm . . . . . . . . ; The output should have data from val9 to val2. Basically i need only 8 values but they should be latest, meaning it has to drop val1. ... View more

I usually try to avoid sorting datasets to reduce disk activity, but this might be a worthy exception:   data have; input (startdate expirydate) (:mmddyy10.) amount ; format startdate expirydate date9.; cards ; 1/1/2010 2/2/2020 10 1/5/2011 2/3/2015 10 1/3/2012 2/2/2023 10 1/4/2013 2/2/2014 10 5/5/2015 2/2/2028 10 1/6/2016 2/2/2032 10 run; proc sort data=have (keep=expirydate amount) out=expiration; by expirydate; run; data want; set expiration (rename=(expirydate=startdate) in=inexp) have (in=inhave); by startdate; cum_amount + ifn(inexp,-1*amount,amount); if inhave; run; ... View more

data have; infile cards expandtabs; input ID Age_Group $ Income; cards; 1 21-30 1000 2 51-60 1200 3 31-40 1400 4 51-60 2020 5 21-30 3000 6 51-60 3000 ; run; data level; do income=1000 to 3000 by 1000; output; end; run; proc sql; create table want as select *,(select count(*) from have where Age_Group=a.Age_Group and income<=a.income) as Cum_count from (select * from (select distinct Age_Group from have),(select income from level)) as a order by 1,2; quit; ... View more

I'm not sure about the interaction between IDs and formation of new groups because some of the info shared in this post seems inconsistent or contradictory. However, if the current group's range of time overlaps the new group's range of time, you can get the desired outcome.   * determine each group's min start and max end; proc summary data=have nway; class group; output out=grp_range (drop=_:) min(start_time)=min_grp_start max(end_time)=max_grp_end; run; * determine new groups based only on overlaps of time; data new_groups; set grp_range; retain new_group 0 min_new_grp_start max_new_grp_end; format min_new_grp_start max_new_grp_end e8601dt19.; * test if current group overlaps the current new_group; * if not, then create a new group and reset the new group dates; if not (min_grp_start <= max_new_grp_end and max_grp_end >= min_new_grp_start) then do; new_group + 1; min_new_grp_start = min_grp_start; max_new_grp_end = max_grp_end; end; * dynamically calc new group min and max; min_new_grp_start = min(min_new_grp_start, min_grp_start); max_new_grp_end = max(max_new_grp_end, max_grp_end); run; Of course, a much larger sample is needed to fully vet this logic. ... View more