BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PriyaSaha
Calcite | Level 5

I have a dataset as below.

Sale_day_nameshort_name
Z ww pp M-F 6a-3pMTWTF
Z tt bb erty M-F 3p-6pMTWTF
Z gg kk pp Sa-Su 6a-8a      S
Z kk pp llkjhg Sa-Su 6a-8a     SS
Z kk pp llkjhg M-SU 6p-12aMTWTFSS
KPI M-Su 12a-2aMTWTFSS
gtyuooo cyytrrf vftyu Sa 7p     S
vghj nkiug njhgg Sa 11p     S
bhjmm jjhg jjll M-Su 7p-12aMTWTFSS
bhjmn mkj njk M-Su 7p-12a     S
bhgfx gghh hjjkm M-Su 7p-12a     
bjim kkoo kkiuuh Th 10p   T 
bjklm njkl M-F 6a-3p     

I need a output like below

For example in first observation Sale_day_name is "Z ww pp M-F 6a-3p" and short_name is MTWTF so it create varible like date day1,day2....day5.

Z gg kk pp Sa-Su 6a-8a like day4=Thusday

like this each row have one condition.I need a less line code macro query for this.

Sale_day_nameshort_nameDay1Day2Day3Day4Day5Day6Day7
Z ww pp M-F 6a-3pMTWTF MondayTuesdayWednesdayThursdayFriday
Z tt bb erty M-F 3p-6pMTWTF MondayTuesdayWednesdayThursdayFriday
Z gg kk pp Sa-Su 6a-8a      S Saturday
Z kk pp llkjhg Sa-Su 6a-8a     SS SaturdaySunday
Z kk pp llkjhg M-SU 6p-12aMTWTFSSMondayTuesdayWednesdayThursdayFridaySaturdaySunday
KPI M-Su 12a-2aMTWTFSSMondayTuesdayWednesdayThursdayFridaySaturdaySunday
gtyuooo cyytrrf vftyu Sa 7p     S Saturday
vghj nkiug njhgg Sa 11p     S Saturday
bhjmm jjhg jjll M-Su 7p-12aMTWTFSSMondayTuesdayWednesdayThursdayFridaySaturdaySunday
bhjmn mkj njk M-Su 7p-12a     S Sunday
bhgfx gghh hjjkm M-Su 7p-12a      MondayTuesdayWednesdayThursdayFridaySaturdaySunday
bjim kkoo kkiuuh Th 10p   T  Thursday
bjklm njkl M-F 6a-3p      MondayTuesdayWednesdayThursdayFriday

Please help me to write it.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I would ignore your short_name field, as it doesn't clearly differentiate between Tuesdays and Thursdays, or Saturdays and Sundays. Instead, I would use the following:

proc format;

  invalue days

   'M'=3

   'T'=4

   'W'=5

   'TH'=6

   'F'=7

   'SA'=8

   'SU'=9

  ;

run;

data want (drop=dayrange);

  set have;

  format dayrange $10.;

  array day(3:9) $;

  dayrange=upcase(scan(Sale_day_name,-2,' '));

  if index(dayrange,'-') then do;

    start=input(scan(dayrange,1,'-'),days.);

    end=input(scan(dayrange,2,'-'),days.);

  end;

  else do;

    start=input(dayrange,days.);

    end=start;

  end;

  do i=start to end;

    day(i)=put(i,downame8.);

  end;

run;

View solution in original post

7 REPLIES 7
art297
Opal | Level 21

I would ignore your short_name field, as it doesn't clearly differentiate between Tuesdays and Thursdays, or Saturdays and Sundays. Instead, I would use the following:

proc format;

  invalue days

   'M'=3

   'T'=4

   'W'=5

   'TH'=6

   'F'=7

   'SA'=8

   'SU'=9

  ;

run;

data want (drop=dayrange);

  set have;

  format dayrange $10.;

  array day(3:9) $;

  dayrange=upcase(scan(Sale_day_name,-2,' '));

  if index(dayrange,'-') then do;

    start=input(scan(dayrange,1,'-'),days.);

    end=input(scan(dayrange,2,'-'),days.);

  end;

  else do;

    start=input(dayrange,days.);

    end=start;

  end;

  do i=start to end;

    day(i)=put(i,downame8.);

  end;

run;

PriyaSaha
Calcite | Level 5

Thanks a lot Arthur for your kind support.Could please guide me how can this query write in macro ?

Regards,

Priya

art297
Opal | Level 21

How do you want to use it? Provide some example data and a pseudo macro call you'd like to use and what you want it to do.

PriyaSaha
Calcite | Level 5

Hi Arthur,

I have a dataset like below

SALE_DAY_NAME

day_name

A fav name Day M-F 6a-3p

MTWTF

A fav name Early M-F 3p-6p

MTWTF

A fav name Early Morn Sa-Su 6a-8a

S

A fav name Early Morn Sa-Su 6a-8a

SS

A fav name Prime M-SU 6p-12a vvv

MTWTFSS

Name M-Su 12a-2a

MTWTFSS

good day name Sa 7p

S

best day name ENCORE Sa 11p

S

best day namet M-Su 7p-12a

MTWTFSS

best day name M-Su 7p-12a

S

best day name M-Su 7p-12a

best day name ORIG Th 10p

T

Daylight ROS M-F 6a-3p

Daylight ROS M-Su 6a-3p gpd

MTWTF

I want a data set like

SALE_DAY_NAME

day_name

day1

day2

day3

day4

day5

day6

day7

A fav name Day M-F 6a-3p

MTWTF

Monday

Tuesday

Wednesday

Thursday

Friday

A fav name Early M-F 3p-6p

MTWTF

Monday

Tuesday

Wednesday

Thursday

Friday

A fav name Early Morn Sa-Su 6a-8a

S

Saturday

A fav name Early Morn Sa-Su 6a-8a

SS

Saturday

Sunday

A fav name Prime M-SU 6p-12a

MTWTFSS

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Name M-Su 12a-2a

MTWTFSS

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

good day name Sa 7p

S

Saturday

best day name ENCORE Sa 11p

S

Saturday

best day namet M-Su 7p-12a

MTWTFSS

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

best day name M-Su 7p-12a

S

Saturday

best day name M-Su 7p-12a

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

best day name ORIG Th 10p

T

Thursday

Daylight ROS M-F 6a-3p

Monday

Tuesday

Wednesday

Thursday

Friday

Daylight ROS M-Su 6a-3p gpd

MTWTF

Monday

Tuesday

Wednesday

Thursday

Friday

                    for example last observation

i                    if  SALE_DAY_NAME= Daylight ROS M-Su 6a-3p gpd and day_name=MTWTF then put value from day1 to day5.

if  SALE_DAY_NAME=  best day name M-Su 7p-12a and day_name='  ' then take M-Su put value from day1 to day7.

                    

                      first we have check the day_name if day_name blank then take the bold part from SALE_DAY_NAME and put value in day1....day7.

Please help me to write the code for above query.

                       Thanks in advance .I am waiting for your replay.

art297
Opal | Level 21

As I mentioned the other day, your day_name field doesn't differentiate between Tuesdays and Thursdays, or Saturdays and Sundays, thus it would be difficult to write code based on its values.

I already suggested code that can accomplish the task, but don't understand how you want to macrotize it.

PriyaSaha
Calcite | Level 5

Hi Arthur,

Above data the day name if S then it Saturdays ,if SS then Saturdays and Sunday.If it is ' T ' then take from SALE_DAY_NAME bold latters.otheif r wise take the position T ,like if it is 2nd position then Tueseday,5th position thusday.If TH then Thusday if TU then tuesday.


Thanks in advance.

Regards,

Priya

art297
Opal | Level 21

You still haven't mentioned how you would like a macro to work, how (and from where) you'd like to call it, what parameters it should be sent (and which will be hardwired). or what day should be selected from the short name if it only contains:

T

or

S

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 1286 views
  • 0 likes
  • 2 in conversation