Good evening.
I wonder if someone could help me? I have data such as "have" from the attachment. I'd like data such as "want" in the attached data. Basically I need to alternate the create date and time columns out for however many (max) based on the records I have.
I'm hoping someone out there has run into something similar or who just may know the right approach I need to take.
Thanks so much for anyone's help.
This accomplishes what you want:
data have;
format record 8. namel $6. namef $6. create_date mmddyy10. create_time time8.;
input record namel $ namef $ create_date :mmddyy10. create_time :time8.;
datalines;
3781 NAMEL1 NAMEF1 11212019 06:40:42
3781 NAMEL1 NAMEF1 11212019 07:07:31
4094 NAMEL2 NAMEF2 11212019 13:10:49
4094 NAMEL2 NAMEF2 11212019 16:38:09
4094 NAMEL2 NAMEF2 11212019 17:13:42
4094 NAMEL2 NAMEF2 11212019 20:33:29
4094 NAMEL2 NAMEF2 11212019 20:33:32
5425 NAMEL3 NAMEF3 11212019 06:22:58
5425 NAMEL3 NAMEF3 11212019 13:32:59
5714 NAMEL4 NAMEF4 11212019 06:23:52
5714 NAMEL4 NAMEF4 11212019 11:34:51
5714 NAMEL4 NAMEF4 11212019 12:01:36
5714 NAMEL4 NAMEF4 11212019 14:51:42
9005 NAMEL5 NAMEF5 11212019 06:05:47
9005 NAMEL5 NAMEF5 11212019 11:23:04
9005 NAMEL5 NAMEF5 11212019 11:54:41
9005 NAMEL5 NAMEF5 11212019 14:49:40
9315 NAMEL6 NAMEF6 11212019 06:35:21
;
run;
proc sort data=have; by record namel namef; run;
*transpose with create_date;
proc transpose data=have out=have_trx_date(drop=_name_) prefix=created_date;
by record namel namef;
var create_date;
run;
*transpose with create_time;
proc transpose data=have out=have_trx_time(drop=_name_) prefix=created_time;
by record namel namef;
var create_time;
run;
*merge the two together;
data merge_results;
merge have_trx_date(in=a) have_trx_time(in=b);
by record namel namef;
if a and b;
run;
*putting created% variable names into macro variable (interleaves date and time);
proc sql noprint;
select name into :created_cols separated by " " from dictionary.columns
where libname="WORK" and memname="MERGE_RESULTS" and name like "created%"
order by compress(name, ,'kd'), name
;quit;
*just re-ordering columns;
data want;
retain record namel namef &created_cols.;
set merge_results;
run;
Going forward, please post your 'have' as a data step like I have done in the first step of the solution.
Let me know if you have any questions. Cheers,
-unison
This accomplishes what you want:
data have;
format record 8. namel $6. namef $6. create_date mmddyy10. create_time time8.;
input record namel $ namef $ create_date :mmddyy10. create_time :time8.;
datalines;
3781 NAMEL1 NAMEF1 11212019 06:40:42
3781 NAMEL1 NAMEF1 11212019 07:07:31
4094 NAMEL2 NAMEF2 11212019 13:10:49
4094 NAMEL2 NAMEF2 11212019 16:38:09
4094 NAMEL2 NAMEF2 11212019 17:13:42
4094 NAMEL2 NAMEF2 11212019 20:33:29
4094 NAMEL2 NAMEF2 11212019 20:33:32
5425 NAMEL3 NAMEF3 11212019 06:22:58
5425 NAMEL3 NAMEF3 11212019 13:32:59
5714 NAMEL4 NAMEF4 11212019 06:23:52
5714 NAMEL4 NAMEF4 11212019 11:34:51
5714 NAMEL4 NAMEF4 11212019 12:01:36
5714 NAMEL4 NAMEF4 11212019 14:51:42
9005 NAMEL5 NAMEF5 11212019 06:05:47
9005 NAMEL5 NAMEF5 11212019 11:23:04
9005 NAMEL5 NAMEF5 11212019 11:54:41
9005 NAMEL5 NAMEF5 11212019 14:49:40
9315 NAMEL6 NAMEF6 11212019 06:35:21
;
run;
proc sort data=have; by record namel namef; run;
*transpose with create_date;
proc transpose data=have out=have_trx_date(drop=_name_) prefix=created_date;
by record namel namef;
var create_date;
run;
*transpose with create_time;
proc transpose data=have out=have_trx_time(drop=_name_) prefix=created_time;
by record namel namef;
var create_time;
run;
*merge the two together;
data merge_results;
merge have_trx_date(in=a) have_trx_time(in=b);
by record namel namef;
if a and b;
run;
*putting created% variable names into macro variable (interleaves date and time);
proc sql noprint;
select name into :created_cols separated by " " from dictionary.columns
where libname="WORK" and memname="MERGE_RESULTS" and name like "created%"
order by compress(name, ,'kd'), name
;quit;
*just re-ordering columns;
data want;
retain record namel namef &created_cols.;
set merge_results;
run;
Going forward, please post your 'have' as a data step like I have done in the first step of the solution.
Let me know if you have any questions. Cheers,
-unison
This response got me closest to what I need. The other solution was to combine the date and the time but the customer wants each separate. I should have specified that in the ask, sorry. Thank you so much for taking the time to help me.
Many people won't download Excels. Best post sample data in the form of a data step as done below.
I would combine the date and time column into a single column containing a SAS DateTime value. I've added such conversion to below code.
You can use SAS formats and SAS functions if you only want to work with the date or time part of a variable. Many SAS procedures also allow you to define using formatted values for grouping and to define the format you want to use directly within the procedure.
data have;
infile datalines dlm='|' dsd truncover;
input (record namel namef) ($) create_date :mmddyy10. create_time :time.;
format create_date mmddyy10. create_time time8.;
datalines;
3781|NAMEL1|NAMEF1|11/21/2019|6:40:42
3781|NAMEL1|NAMEF1|11/21/2019|7:07:31
4094|NAMEL2|NAMEF2|11/21/2019|13:10:49
4094|NAMEL2|NAMEF2|11/21/2019|16:38:09
4094|NAMEL2|NAMEF2|11/21/2019|17:13:42
4094|NAMEL2|NAMEF2|11/21/2019|20:33:29
4094|NAMEL2|NAMEF2|11/21/2019|20:33:32
5425|NAMEL3|NAMEF3|11/21/2019|6:22:58
5425|NAMEL3|NAMEF3|11/21/2019|13:32:59
5714|NAMEL4|NAMEF4|11/21/2019|6:23:52
5714|NAMEL4|NAMEF4|11/21/2019|11:34:51
5714|NAMEL4|NAMEF4|11/21/2019|12:01:36
5714|NAMEL4|NAMEF4|11/21/2019|14:51:42
9005|NAMEL5|NAMEF5|11/21/2019|6:05:47
9005|NAMEL5|NAMEF5|11/21/2019|11:23:04
9005|NAMEL5|NAMEF5|11/21/2019|11:54:41
9005|NAMEL5|NAMEF5|11/21/2019|14:49:40
9315|NAMEL6|NAMEF6|11/21/2019|6:35:21
;
data inter;
set have;
format create_dttm datetime20.;
create_dttm=sum(create_date*86400, create_time);
drop create_date create_time;
run;
proc sort data=inter;
by record namel namef;
run;
proc transpose data=inter out=want(drop=_:) prefix=create_dttm_;
by record namel namef;
var create_dttm;
run;
proc print data=want;
run;
The other response got me closest to what I need. The other solution was to combine the date and the time but the customer wants each separate. I should have specified that in the ask, sorry. Thank you so much for taking the time to help me.
If you combine your dates and times into SAS datetimes, then you only have to transpose
data have;
format record 8. namel $6. namef $6. create_date mmddyy10. create_time time8.;
input record namel $ namef $ create_date :mmddyy10. create_time :time8.;
datalines;
3781 NAMEL1 NAMEF1 11212019 06:40:42
3781 NAMEL1 NAMEF1 11212019 07:07:31
4094 NAMEL2 NAMEF2 11212019 13:10:49
4094 NAMEL2 NAMEF2 11212019 16:38:09
4094 NAMEL2 NAMEF2 11212019 17:13:42
4094 NAMEL2 NAMEF2 11212019 20:33:29
4094 NAMEL2 NAMEF2 11212019 20:33:32
5425 NAMEL3 NAMEF3 11212019 06:22:58
5425 NAMEL3 NAMEF3 11212019 13:32:59
5714 NAMEL4 NAMEF4 11212019 06:23:52
5714 NAMEL4 NAMEF4 11212019 11:34:51
5714 NAMEL4 NAMEF4 11212019 12:01:36
5714 NAMEL4 NAMEF4 11212019 14:51:42
9005 NAMEL5 NAMEF5 11212019 06:05:47
9005 NAMEL5 NAMEF5 11212019 11:23:04
9005 NAMEL5 NAMEF5 11212019 11:54:41
9005 NAMEL5 NAMEF5 11212019 14:49:40
9315 NAMEL6 NAMEF6 11212019 06:35:21
;
data temp;
do id = 1 by 1 until(last.namef);
set have; by record namel namef notsorted;
dt = dhms(create_date, hour(create_time), minute(create_time), second(create_time));
output;
end;
format dt datetime.;
run;
proc transpose data=temp out=want(drop=_name_) prefix=create_dt;
by record namel namef notsorted;
var dt;
id id;
run;
record namel namef create_dt1 create_dt2 create_dt3 create_dt4 create_dt5 3781 NAMEL1 NAMEF1 21NOV19:06:40:42 21NOV19:07:07:31 . . . 4094 NAMEL2 NAMEF2 21NOV19:13:10:49 21NOV19:16:38:09 21NOV19:17:13:42 21NOV19:20:33:29 21NOV19:20:33:32 5425 NAMEL3 NAMEF3 21NOV19:06:22:58 21NOV19:13:32:59 . . . 5714 NAMEL4 NAMEF4 21NOV19:06:23:52 21NOV19:11:34:51 21NOV19:12:01:36 21NOV19:14:51:42 . 9005 NAMEL5 NAMEF5 21NOV19:06:05:47 21NOV19:11:23:04 21NOV19:11:54:41 21NOV19:14:49:40 . 9315 NAMEL6 NAMEF6 21NOV19:06:35:21 . . . .
thanks to @unison for the data.
The other response got me closest to what I need. The other solution was to combine the date and the time but the customer wants each separate. I should have specified that in the ask, sorry. Thank you so much for taking the time to help me.
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.