BookmarkSubscribeRSS Feed
Aar684
Calcite | Level 5
Hi all,

I am back again. I have a file with 365 days of data and 24 hours of each day going horizontally. for example

Date Hour1 Hour2 Hour3 ........Hour24
01/01/10 2.5 5.2 6.4 5.8
01/02/10 8.5 6.2 7.4 5.9
01/03/10 0 7.4 5.7 2.5

What I need to do is move hours 13-24 to a new row with the same date. For example

Date Hour1 Hour2 Hour3 ........Hour12
01/01/10 2.5 5.2 6.4 5.8
01/01/10 8.5 6.2 7.4 5.9 <----- hours 13-24
01/02/10 0 7.4 5.7 2.5
01/02/10

I'm not even sure where to begin here. Since the column headers are the hours, telling SAS to move the columns isn't really an option? Thanks for any suggestions.
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
A DATA step with two OUTPUT statements, one for each unique file. And your DATA statement would have the two files listed with different KEEP= lists (the Hour_ variable values split up), as required.

Scott Barry
SBBWorks, Inc.
Aar684
Calcite | Level 5
Scott,

I need to end up with only one file. When you say use a data step with an output for each unique file, do you mean output hours 1-12 into a data file then output 13-24 into a data file and combine them by date? Or am I interpretting this wrong.
RickM
Fluorite | Level 6
You could try something like the following untested code,

data want;
set have;
array Hour(24) Hour1-Hour24;
output;
do i=1 to 12;
hour(i)=hour(i+12);
end;
output;
drop hour13-hour24;
run;

Good luck!
Aar684
Calcite | Level 5
Rick,

That code works perfectly, however I would like to better understand it. I understand the concept of loops as I have a basic background in C++. I just don't see how the code takes hours 13-24 and moves them below 1-12. The code works, but can you explain it quickly so that I may understand better for next time?
Aar684
Calcite | Level 5
Actually I think I understand. In the second part of the code you output 12 + i. I see I think.
RickM
Fluorite | Level 6
The do loop works like a for loop would in other languages. By default SAS increments the value by 1 so you could have also writen it as:

do i=1 to 12 by 1;
...
end;

So after each iteration the value of i increases by 1.

This loop happens for each row in the original data set. The first output statement writes the entire observation to the new dataset. The loop the shifts each hour value over 12 places. Then the second output statement writes the modified observation to the new dataset. If you dont include the drop statement you will see that the values of hour13-hour24 haven't actually changed.

To learn more about loops in SAS (or SAS in general) go to lexjansen.com which has many papers from SAS conferences.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 865 views
  • 0 likes
  • 3 in conversation