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 ;
...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 ;
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
...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 ;
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 ;
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;
hello kawakami
it works very well
thanks a lot
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.