topic Re: Creating alternating columns from horizontal data in SAS Programming

Creating alternating columns from horizontal data

Jeff_DOC — Sat, 23 Nov 2019 00:22:55 GMT

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.

Re: Creating alternating columns from horizontal data

unison — Sat, 23 Nov 2019 04:31:48 GMT

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

Re: Creating alternating columns from horizontal data

Patrick — Sat, 23 Nov 2019 04:45:05 GMT

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;

Re: Creating alternating columns from horizontal data

PGStats — Sat, 23 Nov 2019 05:15:02 GMT

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.

Re: Creating alternating columns from horizontal data

Jeff_DOC — Mon, 25 Nov 2019 20:45:00 GMT

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.

Re: Creating alternating columns from horizontal data

Jeff_DOC — Mon, 25 Nov 2019 20:45:45 GMT

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.

Re: Creating alternating columns from horizontal data

Jeff_DOC — Mon, 25 Nov 2019 20:46:06 GMT