@LightJade wrote: thank you. but what if I have case when billcode in ('A100', 'A101', 'A102', ..., 'A158' ) then 'Category 1' thank you! I would solve such issues with a custom format: data cntlin_ds; fmtname = "billcode"; type = "C"; label = "category 1"; do i = 1 to 158; start = "A" !! put(i,z3.); output; end; run; proc format cntlin=cntlin_ds; run; proc sql; create table want as select put(billcode,$billcode.) as newvar from have ; quit; Expand the first data step to cover other ranges, and possibly add one observation for "other" with hlo = "O". ... View more

data have; input StudentID (Date_from Date_to) (:mmddyy10.) Sports :$12.; format Date_from Date_to mmddyy10.; cards; 100 1/1/2019 2/28/2019 baseball 100 3/1/2019 3/31/2019 swim 101 2/1/2019 2/28/2019 football 101 3/1/2019 4/30/2019 tennis ; data temp; set have; do date=Date_from to Date_to; output; end; format date mmddyy10.; drop Date_from Date_to; run; proc sort data=temp; by StudentID date; run; data temp2; merge temp temp(keep=StudentID date rename=(StudentID=_StudentID date=_date) firstobs=2); output; if StudentID=_StudentID then do; do date=date+1 to _date-1; output; end; end; drop _: ; run; data temp3; set temp2; group=intnx('month',date,0); format group mmddyy10.; run; proc sql; create table want as select StudentID ,min(date) as from_date format=mmddyy10., max(date) as to_date format=mmddyy10.,max(sports) as sports from temp3 group by StudentID ,group; quit; ... View more

Just use the correct variable name (student_id) in the BY statement. You can avoid such misunderstandings (because I had to make assumptions about your data) by posting data in the proper manner (as SAS code, data steps with datalines). ... View more