cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10 Nasser_DRMCP
Lapis Lazuli | Level 10
Go to Solution

get day name in a transposed table

Posted 09-03-2021 09:28 AM (1092 views)

hello,

what I would like to obtain in this want table is the day name as first row.

that is to say, datas of first row should be : friday, saturday, Sunday, monday…(vendredi, samedi, dimanche ...should be better)

and second row should be 03/09/2021....

by doing that, it will be easier to read when the table is exported into excel

thanks

nass

Data have ;
input ladate result employee $ ;
informat ladate ddmmyy10. ;
format ladate ddmmyy10. ;
datalines ;
03/09/2021 1 robert
04/09/2021 5 robert
05/09/2021 8 robert
06/09/2021 9 robert
07/09/2021 23 robert
08/09/2021 56 robert
09/09/2021 15 robert
10/09/2021 48 robert
11/09/2021 99 robert
12/09/2021 23 robert
13/09/2021 45 robert
14/09/2021 48 robert
15/09/2021 1 robert
16/09/2021 5 robert
17/09/2021 8 robert
18/09/2021 9 robert
19/09/2021 23 robert
20/09/2021 56 robert
21/09/2021 15 robert
22/09/2021 48 robert
23/09/2021 99 robert
24/09/2021 23 robert
25/09/2021 45 robert
26/09/2021 48 robert
27/09/2021 11 robert
28/09/2021 12 robert
29/09/2021 13 robert
30/09/2021 16 robert
;
run ;

proc transpose data=have out=want (drop=_name_);
by employee ;
id ladate ;
var result ;
run ;

 

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
japelin
Rhodochrosite | Level 12 japelin
Rhodochrosite | Level 12

Re: get day name in a transposed table

Posted 09-03-2021 10:27 AM (1063 views)  |   In reply to Nasser_DRMCP

...like this?

data next;
  set have;
  length weekday $9;
  select (weekday(ladate));
    when(1)weekday='Sunday';
    when(2)weekday='Monday';
    when(3)weekday='Tuesday';
    when(4)weekday='Wednesday';
    when(5)weekday='Thursday';
    when(6)weekday='Friday';
    when(7)weekday='Saturday';
    otherwise;
  end;
run;

proc transpose data=next out=want (drop=_name_);
  by employee ;
  id ladate ;
  var weekday ladate result;
run ;

View solution in original post

0 Likes
6 REPLIES 6
maguiremq
SAS Super FREQ maguiremq
SAS Super FREQ

Re: get day name in a transposed table

Posted 09-03-2021 10:18 AM (1070 views)  |   In reply to Nasser_DRMCP

Little confused as to the exact output that you want. Notice that I created a new variable containing the name of the day of the week in your 'have' data set.

Data have ;
input ladate result employee $ ;
day_name = trim(left(put(ladate, downame.)));
informat ladate ddmmyy10. ;
format ladate ddmmyy10. ;
datalines ;
03/09/2021 1 robert
04/09/2021 5 robert
05/09/2021 8 robert
06/09/2021 9 robert
07/09/2021 23 robert
08/09/2021 56 robert
09/09/2021 15 robert
10/09/2021 48 robert
11/09/2021 99 robert
12/09/2021 23 robert
13/09/2021 45 robert
14/09/2021 48 robert
15/09/2021 1 robert
16/09/2021 5 robert
17/09/2021 8 robert
18/09/2021 9 robert
19/09/2021 23 robert
20/09/2021 56 robert
21/09/2021 15 robert
22/09/2021 48 robert
23/09/2021 99 robert
24/09/2021 23 robert
25/09/2021 45 robert
26/09/2021 48 robert
27/09/2021 11 robert
28/09/2021 12 robert
29/09/2021 13 robert
30/09/2021 16 robert
;
run ;

What I read:

proc transpose data=have out=want (drop=_name_);
by employee ladate;
id day_name ;
var result ;
run ;
Obs 	employee 	ladate 	Friday 	Saturday 	Sunday 	Monday 	Tuesday 	Wednesday 	Thursday
1 	robert 	03/09/2021 	1 	. 	. 	. 	. 	. 	.
2 	robert 	04/09/2021 	. 	5 	. 	. 	. 	. 	.
3 	robert 	05/09/2021 	. 	. 	8 	. 	. 	. 	.
4 	robert 	06/09/2021 	. 	. 	. 	9 	. 	. 	.
5 	robert 	07/09/2021 	. 	. 	. 	. 	23 	. 	.
6 	robert 	08/09/2021 	. 	. 	. 	. 	. 	56 	.
7 	robert 	09/09/2021 	. 	. 	. 	. 	. 	. 	15
8 	robert 	10/09/2021 	48 	. 	. 	. 	. 	. 	.
9 	robert 	11/09/2021 	. 	99 	. 	. 	. 	. 	.
10 	robert 	12/09/2021 	. 	. 	23 	. 	. 	. 	.
11 	robert 	13/09/2021 	. 	. 	. 	45 	. 	. 	.
12 	robert 	14/09/2021 	. 	. 	. 	. 	48 	. 	.
13 	robert 	15/09/2021 	. 	. 	. 	. 	. 	1 	.
14 	robert 	16/09/2021 	. 	. 	. 	. 	. 	. 	5
15 	robert 	17/09/2021 	8 	. 	. 	. 	. 	. 	.
16 	robert 	18/09/2021 	. 	9 	. 	. 	. 	. 	.
17 	robert 	19/09/2021 	. 	. 	23 	. 	. 	. 	.
18 	robert 	20/09/2021 	. 	. 	. 	56 	. 	. 	.
19 	robert 	21/09/2021 	. 	. 	. 	. 	15 	. 	.
20 	robert 	22/09/2021 	. 	. 	. 	. 	. 	48 	.
21 	robert 	23/09/2021 	. 	. 	. 	. 	. 	. 	99
22 	robert 	24/09/2021 	23 	. 	. 	. 	. 	. 	.
23 	robert 	25/09/2021 	. 	45 	. 	. 	. 	. 	.
24 	robert 	26/09/2021 	. 	. 	48 	. 	. 	. 	.
25 	robert 	27/09/2021 	. 	. 	. 	11 	. 	. 	.
26 	robert 	28/09/2021 	. 	. 	. 	. 	12 	. 	.
27 	robert 	29/09/2021 	. 	. 	. 	. 	. 	13 	.
28 	robert 	30/09/2021 	. 	. 	. 	. 	. 	. 	16

What looks a little cleaner:

proc transpose data=have out=want2 (drop=_name_);
by employee ladate day_name ;
var result ;
run ;
Obs 	employee 	ladate 	day_name 	COL1
1 	robert 	03/09/2021 	Friday 	1
2 	robert 	04/09/2021 	Saturday 	5
3 	robert 	05/09/2021 	Sunday 	8
4 	robert 	06/09/2021 	Monday 	9
5 	robert 	07/09/2021 	Tuesday 	23
6 	robert 	08/09/2021 	Wednesday 	56
7 	robert 	09/09/2021 	Thursday 	15
8 	robert 	10/09/2021 	Friday 	48
9 	robert 	11/09/2021 	Saturday 	99
10 	robert 	12/09/2021 	Sunday 	23
11 	robert 	13/09/2021 	Monday 	45
12 	robert 	14/09/2021 	Tuesday 	48
13 	robert 	15/09/2021 	Wednesday 	1
14 	robert 	16/09/2021 	Thursday 	5
15 	robert 	17/09/2021 	Friday 	8
16 	robert 	18/09/2021 	Saturday 	9
17 	robert 	19/09/2021 	Sunday 	23
18 	robert 	20/09/2021 	Monday 	56
19 	robert 	21/09/2021 	Tuesday 	15
20 	robert 	22/09/2021 	Wednesday 	48
21 	robert 	23/09/2021 	Thursday 	99
22 	robert 	24/09/2021 	Friday 	23
23 	robert 	25/09/2021 	Saturday 	45
24 	robert 	26/09/2021 	Sunday 	48
25 	robert 	27/09/2021 	Monday 	11
26 	robert 	28/09/2021 	Tuesday 	12
27 	robert 	29/09/2021 	Wednesday 	13
28 	robert 	30/09/2021 	Thursday 	16
0 Likes
japelin
Rhodochrosite | Level 12 japelin
Rhodochrosite | Level 12

Re: get day name in a transposed table

Posted 09-03-2021 10:27 AM (1064 views)  |   In reply to Nasser_DRMCP

...like this?

data next;
  set have;
  length weekday $9;
  select (weekday(ladate));
    when(1)weekday='Sunday';
    when(2)weekday='Monday';
    when(3)weekday='Tuesday';
    when(4)weekday='Wednesday';
    when(5)weekday='Thursday';
    when(6)weekday='Friday';
    when(7)weekday='Saturday';
    otherwise;
  end;
run;

proc transpose data=next out=want (drop=_name_);
  by employee ;
  id ladate ;
  var weekday ladate result;
run ;
0 Likes
Nasser_DRMCP
Lapis Lazuli | Level 10 Nasser_DRMCP
Lapis Lazuli | Level 10

Re: get day name in a transposed table

Posted 09-03-2021 11:44 AM (1019 views)  |   In reply to japelin

Hello kawakami

many thanks for your respons il works well

I just notice that when I have several employees (there, robert and Alice).then I get many rows with weekday

if I could get only one it could be great ! thanks

Data have ;
input ladate result employee $ ;
day_name = trim(left(put(ladate, downame.)));
informat ladate ddmmyy10. ;
format ladate ddmmyy10. ;
datalines ;
03/09/2021 1 robert
04/09/2021 5 robert
05/09/2021 8 robert
06/09/2021 9 robert
07/09/2021 23 robert
08/09/2021 56 robert
09/09/2021 15 robert
10/09/2021 48 robert
11/09/2021 99 robert
12/09/2021 23 robert
13/09/2021 45 robert
14/09/2021 48 robert
15/09/2021 1 robert
16/09/2021 5 robert
17/09/2021 8 robert
18/09/2021 9 robert
19/09/2021 23 robert
20/09/2021 56 robert
21/09/2021 15 robert
22/09/2021 48 robert
23/09/2021 99 robert
24/09/2021 23 robert
25/09/2021 45 robert
26/09/2021 48 robert
27/09/2021 11 robert
28/09/2021 12 robert
29/09/2021 13 robert
30/09/2021 16 robert
03/09/2021 1 alice
04/09/2021 5 alice
05/09/2021 8 alice
06/09/2021 9 alice
07/09/2021 23 alice
08/09/2021 56 alice
09/09/2021 15 alice
10/09/2021 48 alice
11/09/2021 99 alice
12/09/2021 23 alice
13/09/2021 45 alice
14/09/2021 48 alice
15/09/2021 1 alice
16/09/2021 5 alice
17/09/2021 8 alice
18/09/2021 9 alice
19/09/2021 23 alice
20/09/2021 56 alice
21/09/2021 15 alice
22/09/2021 48 alice
23/09/2021 99 alice
24/09/2021 23 alice
25/09/2021 45 alice
26/09/2021 48 alice
27/09/2021 11 alice
28/09/2021 12 alice
29/09/2021 13 alice
30/09/2021 16 alice
;
run ;
0 Likes
japelin
Rhodochrosite | Level 12 japelin
Rhodochrosite | Level 12

Re: get day name in a transposed table

Posted 09-03-2021 12:14 PM (1005 views)  |   In reply to Nasser_DRMCP

I can't think of a smart way to do that.

 

proc transpose data=next out=next1(where=(_name_ ='result'));
  by employee ;
  id ladate ;
  var result weekday ladate ;
run ;
proc transpose data=next out=next2(where=(_name_^='result'));
  by employee ;
  id ladate ;
  var result weekday ladate ;
run ;
proc sort data=next2 nodupkey;
  by _name_;
run;
data next2;
  set next2;
  by _name_;
  employee='';
  if first._name_;
run;
data want;
  set next2 next1;
  drop _name_;
run;
0 Likes
Nasser_DRMCP
Lapis Lazuli | Level 10 Nasser_DRMCP
Lapis Lazuli | Level 10

Re: get day name in a transposed table

Posted 09-06-2021 04:52 AM (908 views)  |   In reply to japelin

hello kawakami

it works very well

thanks a lot

0 Likes
ballardw
Super User ballardw
Super User

Re: get day name in a transposed table

Posted 09-03-2021 12:15 PM (999 views)  |   In reply to Nasser_DRMCP
@Nasser_DRMCP wrote:

Hello kawakami

many thanks for your respons il works well

I just notice that when I have several employees (there, robert and Alice).then I get many rows with weekday

if I could get only one it could be great ! thanks

Provide an example of what your output should look like as well. Perhaps using a somewhat smaller input set will make that easier.

0 Likes

sas-innovate-white.png

🚨 Early Bird Rate Extended!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Lock in the best rate now before the price increases on April 1.

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎09-03-2021 09:28 AM
  • 1093 views
  • 0 likes
  • 4 in conversation