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!
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;
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;
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!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.