Probably not the most efficient but I think it will work for you. data work.have; infile datalines; input Action $ State $ Date1 :mmddyy10. Date2 :mmddyy10.; format Date1 mmddyy10. Date2 mmddyy10.; datalines; Walk AK 04/01/2020 04/09/2020 Run AL 03/30/2020 04/03/2020 Walk AR 03/26/2020 04/02/2020 Walk AZ 04/03/2020 04/08/2020 Sit CA 03/28/2020 04/01/2020 Run CO 04/01/2020 04/09/2020 ; run; data expand_have; set have; format date mmddyy10.; do date = date1 to date2; action_value=0; if action="Run" then action_value=2; else if action="Walk" then action_value=1; else if action="Sit" then action_value=3; output; end; drop action date1 date2; run; proc sql noprint; create table min_max as select min(date1) as min_date1 format=mmddyy10., max(date1) as max_date1 format=mmddyy10., min(date2) as min_date2 format=mmddyy10., max(date2) as max_date2 format=mmddyy10. from have; select min(min_date1,min_date2) into :min_date from min_max; select max(max_date1,max_date2) into :max_date from min_max; select distinct(state) into :states separated by ',' from have; select count(distinct(state)) into :statecount from have; quit; run; data _null_; put "States is &states."; put "Count is &statecount."; put "Min date is &min_date."; put "Max date is &max_date."; stop; run; data stage; format date mmddyy10.; format state $2.; states = "&states."; do date = &min_date to &max_date; do i = 1 to &statecount; state = trim(scan(states,i)); output; end; end; stop; drop states i; run; proc sql; create table need as select a.*,coalesce(b.action_value,0) as action from stage a left join expand_have b on a.state = b.state and a.date=b.date order by date, state; quit; run; ... View more

Edited original post. I just need to be able to expand the counties in the dataset to have all counties included (could merge with some county reference file). ... View more

@wernie wrote: I’m just trying to collapse the age groups to get <50, 50-65, 65+ and get the population total for those groups. The GEO_ID a unique ID so each GEO_ID needs to have the age groups collapsed and populations summed. This doesn't tell us anything more than the original message. How do you collapse groups numbered 1 to 10 into <50, 60-65, 65+ ?? ... View more

Thanks everyone! I got it sorted out! The log had a warning about 'Multiple lengths were specified for the variable Temp_Agg by input data set(s). This can cause truncation of data.'   I went to an earlier macro step and set it so all datasets would have the same length for the Temp_Agg variable and that then allowed it to apply that format properly. ... View more

Thanks everyone! I was replicating a process that someone else used and they did that, so I was trying to do the same thing. I'm not entirely sure why they did that though as it looks like they ended up putting it back into a regular date and just used the format that I used way back when I imported the data. I'll keep looking through to see if it's really necessary, as mentioned. ... View more

I did try doing something, but I don't have the code in front of me so can't quite recall what I did. I do know I was trying to do proc SQL to merge and I don't think I did it on county and date. I'll try this code and see if that gives what I need. Good point on the value columns and renaming them. Thanks! ... View more

PROC MEANS has a weight option, SQL would make you do the calculation manually. 26 million rows shouldn't be an issue unless you're using SAS UE. ... View more