BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlegge
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
r_behata
Barite | Level 11
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;

View solution in original post

4 REPLIES 4
r_behata
Barite | Level 11
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;
mlegge
Fluorite | Level 6
Awesome, works like a charm! Thank you!
Reeza
Super User

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!




 

 

 

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is ANOVA?

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.

Discussion stats
  • 4 replies
  • 782 views
  • 4 likes
  • 3 in conversation