Re: PROC SQL Question

I largely concur with the advice offered by Sig and Ian. This is something
you might do in a report, but it's not a good idea in a table intended for
subsequent processing. PROC SQL's reporting capabilities are limited; PROC
PRINT (not to mention PROC REPORT) is more versatile.

Nevertheless, PROC SQL is up to this particular task. Carol said that there
is a variable which explicitly orders the rows in each group. I'll call
that SEQ.

So here's a modified test data set:

data demo;
input JobName $ StartTime EndTime RunTime Seq;
cards;
ABC 0111 0159 48 101
ABC 0106 0128 22 104
ABC 0333 0356 23 105
DEF 0110 0220 70 103
DEF 1400 1405 05 107
JKL 0800 0805 05 102
;

Here is the SQL code

proc sql;
select case when Seq=min(Seq) then JobName
else ''
end label= 'Job Name',
StartTime,
EndTime,
RunTime
from demo
group by demo.JobName
order by demo.JobName, Seq
;
quit;

Here is the output:

Job Name StartTime EndTime RunTime

ABC 111 159 48
106 128 22
333 356 23
DEF 110 220 70
1400 1405 5
JKL 800 805 5

On Tue, 4 Oct 2005 07:39:21 -0400, Srna, Carol (C.) <csrna@FORD.COM> wrote:

>Thanks for responding.
>
>1). No
>
>2). Yes
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Howard Schreier <hs AT dc-sug DOT org>
>Sent: Monday, October 03, 2005 12:18 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: PROC SQL Question
>
>
>Does the initial ordering of rows within each JobName group have to be
>preserved? If so, is there some variable which explicitly tracks that
>ordering?
>
>About the timestamps: Is it conceivable that a row will have a StartTime
>before midnight and an EndTime after midnight and thus on a different
>calendar day? If so, you won't like your computed RunTime results.
>
>On Mon, 3 Oct 2005 08:30:04 -0400, Srna, Carol (C.) <csrna@FORD.COM>
>wrote:
>
>>Hi All.
>>
>>Input data:
>>
>>JobName StartTime EndTime RunTime
>>ABC 0111 0159
>>48
>>ABC 0106 0128
>>22
>>ABC 0333 0356
>>23
>>
>>DEF 0110 0220
>>10
>>DEF 1400 1405
>>05
>>
>>JKL 0800 0805
>>05
>>
>>This is how I want the output to look:
>>JobName StartTime EndTime RunTime
>>ABC 0111 0159
>>48
>> 0106 0128
>>22
>> 0333 0356
>>23
>>
>>DEF 0110 0220
>>10
>> 1400 1405
>>05
>>
>>JKL 0800 0805
>>05
>>
>>My Code:
>> DATA ALL3;
>> SET ALL2;
>> JOBNAME=SUBSTR(PLINE,17,8);
>> START=SUBSTR(PLINE,66,4);
>> END=SUBSTR(PLINE,77,4);
>> RUNTIME=END-START;
>> RUN;
>>
>> PROC SQL;
>> CREATE TABLE OUT1 AS
>> SELECT JOBNAME, START, END, RUNTIME
>> FROM ALL3
>> GROUP BY JOBNAME;
>> RUN;
>>
>> PROC PRINT;VAR JOBNAME START END RUNTIME;
>> RUN;
>>
>>Do I need to create a TABLE?
>>How do I use DISTINCT for variable JOBNAME only, and also SELECT the
>>other variables for output?
>>
>>Bottom Line:
>>How should this be done?
>>
>>TIA