topic Re: Splitting one column values into seven columns in SAS Programming

Splitting one column values into seven columns

cho16 — Thu, 09 Feb 2023 05:12:41 GMT

Hi Team,

One column that has weekdays, timings, and a delimiter . what I want to divide into seven columns based on the delimiter ,. Additionally, the values are separated by a comma. It was closed on some of the days.

The data I have looks like this:

data have;
length days $200;
infile datalines truncover;
input days $ 1-200;
datalines;
Sun CLOSED, Mon 9:00AM-6:30PM, Tue 9:00AM-6:30PM, Wed 9:00AM-6:30PM, Thu 9:00AM-6:30PM, Fri 9:00AM-6:30PM, Sat 9:00AM-4:00PM,
Sun 10:00AM-9:00PM, Mon 8:00AM-9:00PM, Tue 8:00AM-9:00PM, Wed 8:00AM-9:00PM, Thu 8:00AM-9:00PM, Fri 8:00AM-9:00PM, Sat 8:00AM-9:00PM,
Sun CLOSED, Mon 8:00AM-5:00PM, Tue 8:00AM-5:00PM, Wed 8:00AM-5:00PM, Thu 8:00AM-5:00PM, Fri 8:00AM-5:00PM, Sat CLOSED,
;
run;

I want data as

I tried the below code

data want (drop=days);
set have;
Sunday = scan(days,1,',');
monday = scan(days,2,',');
tuesday = scan(days,3,',');
wednesday = scan(days,4,',');
thursday = scan(days,5,',');
friday = scan(days,6,',');
saturday = scan(days,7,',');
run;

Re: Splitting one column values into seven columns

ballardw — Thu, 09 Feb 2023 05:58:29 GMT

One way:

data want (drop=days);
set have;
length sunday monday tuesday wednesday thursday friday saturday $ 20;
Sunday    = scan( scan(days,1,','),2,' ');
monday    = scan( scan(days,2,','),2,' ');
tuesday   = scan( scan(days,3,','),2,' ');
wednesday = scan( scan(days,4,','),2,' ');
thursday  = scan( scan(days,5,','),2,' ');
friday    = scan( scan(days,6,','),2,' ');
saturday  = scan( scan(days,7,','),2,' ');
run;

You have two pieces and even stated then, the "days" are separated by commas, but the TIME part is separated by a space inside the day value. So nesting the Scan function twice, the inner part gets the "day" of week and the second, using the space delimiter gets the time.

Note the use of the Length statement to specify the length of the variables. If you don't use such you can have unexpected results of results.

Personally I'm not sure if I would trust such data to always be in order and assume that the 4th column is always Wednesday but would parse the values a bit.

It is a likely a poor idea to store actual content (name of the day of the week) in a variable name but you don't indicate what this will be used for later.

Re: Splitting one column values into seven columns

s_lassen — Thu, 09 Feb 2023 12:22:41 GMT

If, as @ballardw suggested, the days are not always in the same order, you should look for the day identifier, e.g.:

data want;                                                                                                                              
  set have;                                                                                                                             
  length sunday monday tuesday wednesday thursday friday saturday $ 20;                                                                 
  array outvar(*) sunday--saturday;                                                                                                     
  _N_=1;                                                                                                                                
  do day='sun','mon','tue','wed','thu','fri','sat';                                                                                     
    pos=findw(days,day,', ','i');                                                                                                       
    if pos then                                                                                                                         
      outvar(_N_)=scan(substr(days,pos),2,', ');                                                                                        
    _N_+1;                                                                                                                              
    end;                                                                                                                                
  drop day pos;                                                                                                                         
run;

Re: Splitting one column values into seven columns

PaigeMiller — Thu, 09 Feb 2023 12:35:38 GMT

As @ballardw says: "It is a likely a poor idea to store actual content (name of the day of the week) in a variable name but you don't indicate what this will be used for later." I would agree, and I advise @cho16 not to do this, and to explain why the data is needed in this form. From everything I know about SAS, data is almost never needed in this form, most (all?) SAS PROCs are designed to work with data in the long form. Arranging data with columns whose names are Monday, Tuesday, etc. just makes programming the next step harder.