topic Re: PROC SQL with TIME manipulation in SAS Procedures

PROC SQL with TIME manipulation

alterman1 — Fri, 27 Apr 2018 20:55:34 GMT

I'm working with instrumentation data.

I have several problems that I don't know how to deal with.

I have some records that contain time values. They aren't clock times, they are durations (like elapsed time). I've looked through a bunch of SAS doc and I haven't found what I'm looking for.

According to proc contents, the fields are defined 'num 5' and they have formats of time12.2. I want to use PROC SQL for a series of queries that I need to run. I need to do two things. I need to add several time variables on each row, subtract that result from another time variable on that row, and then I need to sum those rows - to compute various totals. This is the general problem, but I want to take this one step further and unclutter my SQL.

proc sql;

select

datepart(smftime) as system_date format yymmddd10.,

qwhsssid as ssid,

sum(qpactjst) as cpu_time,

count(*) as count

from db2acctp

where qwhsssid = 'DB2A'

group by ssid, system_date

The SQL above is from a different thread and this is a question for the having to do with this question and SQL I copied into this thread. The main reason I'm asking this is when I start adding fields to my SQL, it's going to be a cluttered mess.

So, my second question is whether there is a way in SAS, that I can declare the format of a column ahead of the SQL so that I only need the column (and may the 'AS' verb) in the SQL. For this thread, I'm going to have to sum 10 variables and then subtract these 10 variables from another variable to get this value for every row and take the results (about 100K rows) and sum the result like the SQL above.

Re: PROC SQL with TIME manipulation

Reeza — Fri, 27 Apr 2018 20:58:55 GMT

Can you include some sample data and expected output?

@alterman1 wrote:

I'm working with instrumentation data.

I have several problems that I don't know how to deal with.

I have some records that contain time values. They aren't clock times, they are durations (like elapsed time). I've looked through a bunch of SAS doc and I haven't found what I'm looking for.

According to proc contents, the fields are defined 'num 5' and they have formats of time12.2. I want to use PROC SQL for a series of queries that I need to run. I need to do two things. I need to add several time variables on each row, subtract that result from another time variable on that row, and then I need to sum those rows - to compute various totals. This is the general problem, but I want to take this one step further and unclutter my SQL.

proc sql;

select

datepart(smftime) as system_date format yymmddd10.,

qwhsssid as ssid,

sum(qpactjst) as cpu_time,

count(*) as count

from db2acctp

where qwhsssid = 'DB2A'

group by ssid, system_date

The SQL above is from a different thread and this is a question for the having to do with this question and SQL I copied into this thread. The main reason I'm asking this is when I start adding fields to my SQL, it's going to be a cluttered mess.

So, my second question is whether there is a way in SAS, that I can declare the format of a column ahead of the SQL so that I only need the column (and may the 'AS' verb) in the SQL. For this thread, I'm going to have to sum 10 variables and then subtract these 10 variables from another variable to get this value for every row and take the results (about 100K rows) and sum the result like the SQL above.

Re: PROC SQL with TIME manipulation

ballardw — Fri, 27 Apr 2018 23:04:24 GMT

Concrete examples help. Depending on what your actual calculations need to be perhaps sql isn't the best approach.

If a variable is reported by contents as having a TIME format then the value is a number of seconds (and fractions of second). So adding durations is the total duration. The format controls how the value is displayed to us humans so we can look at it and say 4 hours and 8 minutes instead of having to do division and such.

You want to be careful using sql and Sum if attempting to add variables together as Sum in Sql is more of an aggregate across records function.

Perhaps a data step is a better approach if you are worried about a "cluttered mess" in code. Arrays and variable lists, features not available in Proc SQL, may make the code for the first step much cleaner.