topic SQL? in SAS Programming

SQL?

Aar684 — Tue, 02 Nov 2010 13:33:37 GMT

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.

Re: SQL?

sbb — Tue, 02 Nov 2010 14:19:26 GMT

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.

Re: SQL?

Aar684 — Tue, 02 Nov 2010 14:25:27 GMT

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.

Re: SQL?

RickM — Tue, 02 Nov 2010 14:36:31 GMT

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!

Re: SQL?

Aar684 — Tue, 02 Nov 2010 14:52:05 GMT

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?

Re: SQL?

Aar684 — Tue, 02 Nov 2010 14:53:23 GMT

Actually I think I understand. In the second part of the code you output 12 + i. I see I think.

Re: SQL?

RickM — Tue, 02 Nov 2010 15:10:02 GMT

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.