topic Re: roll out key value per hour in SAS Enterprise Guide

roll out key value per hour

HansSteenhuis — Thu, 07 Oct 2021 11:49:03 GMT

Hello everyone,

Is it possible in EG to create a formula in the red marked columns? (attachment in excel)

So that the "key value" is filled at the hour of the column "start hour" and that it is expanded with the number of hours of the column "amount of hours"

Re: roll out key value per hour

Kurt_Bremser — Thu, 07 Oct 2021 12:16:45 GMT

PROC REPORT does this from a long dataset:

data have;
input test_number $ hours start_hour key_value;
datalines;
21085484 5 13 .8
25874521 3 15 .75
;

data long;
set have;
do hr = start_hour to start_hour + hours - 1;
  output;
end;
run;

proc report data=long;
column test_number hours start_hour key_value,hr;
define test_number / group;
define hours / group;
define start_hour / group;
define key_value / sum;
define hr / "" across;
run;

If you want to have all 24 columns even if no values are present, use the PRELOADFMT option:

data cntlin;
fmtname = "myhr";
type = "N";
do start = 0 to 23;
  label = put(start,z2.);
  output;
end;
run;

proc format cntlin=cntlin;
run;

proc report data=long;
column test_number hours start_hour key_value,hr;
define test_number / group;
define hours / group;
define start_hour / group;
define key_value / sum;
define hr / "" across format=myhr. preloadfmt;
run;

Re: roll out key value per hour

ballardw — Thu, 07 Oct 2021 12:17:12 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software or organization policy. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: roll out key value per hour

HansSteenhuis — Thu, 07 Oct 2021 12:39:11 GMT

thank you very much

is it possible for this to be a table?

Re: roll out key value per hour

Kurt_Bremser — Thu, 07 Oct 2021 12:58:11 GMT

You can use ODS Excel to present this report to people who don't use SAS; for working with data, the long dataset layout is always more useful.

If you still want a wide dataset, PROC TRANSPOSE does this, but you need a dummy observation to force all hours from 0 to 23:

data have;
infile datalines dsd dlm=" ";
input test_number $ hours start_hour key_value;
datalines;
 23 0 .
21085484 5 13 .8
25874521 3 15 .75
;

data long;
set have;
do hr = start_hour to start_hour + hours - 1;
  output;
end;
run;

proc transpose
  data=long
  out=want (drop=_name_ where=(test_number ne " "))
;
by test_number hours start_hour;
var key_value;
id hr;
run;

Re: roll out key value per hour

HansSteenhuis — Thu, 07 Oct 2021 13:08:19 GMT

thank you very much

Re: roll out key value per hour

HansSteenhuis — Thu, 07 Oct 2021 13:56:07 GMT

I do not get it

I use this table:

Then i use this code:

data have; infile datalines dsd dlm=" "; input test_number $ hours start_hour key_value; datalines; 23 0 . 21085484 5 13 .8 25874521 3 15 .75 ; data long; set _TEST_FORUM; do hr = start_hour to start_hour + hours - 1; output; end; run; proc transpose data=long out=want (drop=_name_ where=(test_number ne " ")) ; by test_number hours start_hour; var key_value; id hr; run;

But now I only get errors

I must be doing something wrong

Re: roll out key value per hour

Kurt_Bremser — Thu, 07 Oct 2021 14:08:38 GMT

For the BY to work properly, the dataset must be sorted in ascending sequence.

My DATA HAVE step is there to create data for testing and illustrating. To prepend a dummy observation to your existing dataset (so that the TRANSPOSE works as intended), do this:

data dummy;
set test_forum; /* get the column attributes */
test_number = 0; /* why do you store somemthing that will never be used in calculations as a number? */
hours = 23;
start_hour = 0;
key_value = .;
output;
stop;
run;

data test_forum;
set
  dummy
  test_forum
;
run;

proc sort data=test_forum;
by test_number;
run;

data long;
set test_forum;
do hr = start_hour to start_hour + hours - 1;
  output;
end;
run;

proc transpose
  data=long
  out=want (drop=_name_ where=(test_number ne 0))
;
by test_number hours start_hour;
var key_value;
id hr;
run;

Note that the "data step with datalines" is the ONLY(!) method to properly present source data here on the communities, as it lets everybody recreate a dataset with a simple copy/paste and submit, without having to code that on our own and make guesses along the way.

If you want working and tested code, properly presented data is a MUST.

Re: roll out key value per hour

HansSteenhuis — Fri, 08 Oct 2021 07:03:16 GMT

Thank you very much, great!

If I have a start time of 21 and it runs for 8 hours. Then the code continues to count.

Can that also be solved?

A test can never last longer than 24 hours.

Re: roll out key value per hour

Kurt_Bremser — Fri, 08 Oct 2021 09:31:45 GMT

When you have time series spanning over midnight, you should consider adding a day indicator to the hour, eitherby using datetimes or a combined day-hour string. Otherwise the software will always put the hours after midnight to the left of the time series start.