topic Re: get day name in a transposed table in SAS Programming

get day name in a transposed table

Nasser_DRMCP — Fri, 03 Sep 2021 13:28:13 GMT

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 ;

Re: get day name in a transposed table

maguiremq — Fri, 03 Sep 2021 14:18:22 GMT

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

Re: get day name in a transposed table

japelin — Fri, 03 Sep 2021 14:27:48 GMT

...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 ;

Re: get day name in a transposed table

Nasser_DRMCP — Fri, 03 Sep 2021 15:44:17 GMT

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 ;

Re: get day name in a transposed table

japelin — Fri, 03 Sep 2021 16:14:20 GMT

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;

Re: get day name in a transposed table

ballardw — Fri, 03 Sep 2021 16:15:10 GMT

@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.

Re: get day name in a transposed table

Nasser_DRMCP — Mon, 06 Sep 2021 08:52:41 GMT

hello kawakami

it works very well

thanks a lot