BookmarkSubscribeRSS Feed
cho16
Obsidian | Level 7

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

cho16_0-1675919520271.png

 

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;

 

3 REPLIES 3
ballardw
Super User

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.

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
s_lassen
Meteorite | Level 14

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;                                                                                                                                    
                  

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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