topic Re: Splitting multiple fields from one row into multiple rows in Statistical Procedures

Splitting multiple fields from one row into multiple rows

mlegge — Fri, 01 May 2020 01:00:59 GMT

Good day everyone, this is my first post and I'm still fairly new to SAS, so please be gentle 🙂

Initial data:

NO ID DATE TIME1 LAT1 LONG1 TIME2 LAT2 LONG2
01 BB 3Mar20 10:05 44.10 -52.01 10:59 44.15 -52.11
02 CC 3Mar20 11:15 44.20 -52.22 12:08 44.22 -52.32
03 DD 3Mar20 12:09 45.30 -52.33 13:44 44.33 -52.43
04 EE 3Mar20 14:22 44.40 -52.44 15:33 44.55 -52.77

NO ID DATE TIME LAT LONG
01 BB 3Mar20 10:05 44.10 -52.01
02 BB 3Mar20 10:59 44.15 -52.11
03 CC 3Mar20 11:15 44.20 -52.22
04 CC 3Mar20 12:08 44.22 -52.32
05 DD 3Mar20 12:09 45.30 -52.33
06 DD 3Mar20 13:44 44.33 -52.43
07 EE 3Mar20 14:22 44.40 -52.44
08 EE 3Mar20 15:33 44.55 -52.77

So basically I want to keep all 1's (TIME, LAT, & LONG) on the first line and put all 2's on the second line.

I've tried playing around with Transpose but to no success!

Re: Splitting multiple fields from one row into multiple rows

r_behata — Fri, 01 May 2020 01:49:36 GMT

data have;
input NO ID $ DATE : date9. TIME1:time5. LAT1 LONG1 TIME2:time5. LAT2 LONG2;
cards4;
01 BB 3Mar20 10:05 44.10 -52.01 10:59 44.15 -52.11
02 CC 3Mar20 11:15 44.20 -52.22 12:08 44.22 -52.32
03 DD 3Mar20 12:09 45.30 -52.33 13:44 44.33 -52.43
04 EE 3Mar20 14:22 44.40 -52.44 15:33 44.55 -52.77
;;;;
run;

Data want;	
	set have(keep=  ID DATE TIME1 LAT1 LONG1 rename=(TIME1=TIME LAT1=LAT LONG1=LONG))
		have(keep=  ID DATE TIME2 LAT2 LONG2 rename=(TIME2=TIME LAT2=LAT LONG2=LONG));
	by  ID DATE;
	format DATE date9.;
	NO= _n_;
run;

Re: Splitting multiple fields from one row into multiple rows

Reeza — Fri, 01 May 2020 01:58:03 GMT

If you need to generalize this beyond 2 records arrays are your best bet, if it's just two @r_behata solution is easier.

data want;
set have;

array _time(*) ;
array _long(*);
array _lat(*);

do i=1 to dim(_time);
    time = time(i);
    long = long(i);
    lat = lat(i);

output;
end;

keep NO ID Date Time Long Lat;
run;

The second link below has descriptions if you need more details. The first link shows how you can use PROC TRANSPOSE to do this, but you'll see quickly it's more work so the array or SET approach is easier.

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

@mlegge wrote:

Good day everyone, this is my first post and I'm still fairly new to SAS, so please be gentle 🙂

Initial data:

NO ID DATE TIME1 LAT1 LONG1 TIME2 LAT2 LONG2
01 BB 3Mar20 10:05 44.10 -52.01 10:59 44.15 -52.11
02 CC 3Mar20 11:15 44.20 -52.22 12:08 44.22 -52.32
03 DD 3Mar20 12:09 45.30 -52.33 13:44 44.33 -52.43
04 EE 3Mar20 14:22 44.40 -52.44 15:33 44.55 -52.77

NO ID DATE TIME LAT LONG
01 BB 3Mar20 10:05 44.10 -52.01
02 BB 3Mar20 10:59 44.15 -52.11
03 CC 3Mar20 11:15 44.20 -52.22
04 CC 3Mar20 12:08 44.22 -52.32
05 DD 3Mar20 12:09 45.30 -52.33
06 DD 3Mar20 13:44 44.33 -52.43
07 EE 3Mar20 14:22 44.40 -52.44
08 EE 3Mar20 15:33 44.55 -52.77

So basically I want to keep all 1's (TIME, LAT, & LONG) on the first line and put all 2's on the second line.

I've tried playing around with Transpose but to no success!

Re: Splitting multiple fields from one row into multiple rows

mlegge — Fri, 01 May 2020 02:59:07 GMT

Awesome, works like a charm! Thank you!

Re: Splitting multiple fields from one row into multiple rows

mlegge — Fri, 01 May 2020 03:00:02 GMT

Thanks for the quick reply, I'll keep your solution in mind if I need to keep 3 records or more!