Make an output based on advanced calculations

[mmea]

Hi I have this kindof dataset - just much bigger, but the structure is like this:

METHOD	Region	PLACE	Dato	Value
B	H	testplace 1	11-01-2021	84
A	H	testplace 1	11-01-2021	42
B	H	testplace 1	13-01-2021	93
A	H	testplace 1	13-01-2021	76
B	H	testplace 1	14-01-2021	91
A	H	testplace 1	14-01-2021	81
B	H	testplace 1	15-01-2021	97
A	H	testplace 1	15-01-2021	1
B	H	testplace 1	16-01-2021	96
A	H	testplace 1	16-01-2021	93
B	H	testplace 1	17-01-2021	99
A	H	testplace 1	17-01-2021	97
B	H	testplace 1	18-01-2021	100
A	H	testplace 1	18-01-2021	98
B	H	testplace 1	19-01-2021	100
A	H	testplace 1	19-01-2021	98
B	H	testplace 1	20-01-2021	2
A	H	testplace 1	20-01-2021	98
B	H	testplace 2	11-01-2021	100
A	H	testplace 2	11-01-2021	56
B	H	testplace 2	13-01-2021	100
A	H	testplace 2	13-01-2021	84
B	H	testplace 2	14-01-2021	100
A	H	testplace 2	14-01-2021	87
B	H	testplace 2	15-01-2021	100
A	H	testplace 2	15-01-2021	89
B	H	testplace 2	16-01-2021	100
A	H	testplace 2	16-01-2021	2
B	H	testplace 2	17-01-2021	100
A	H	testplace 2	17-01-2021	97
B	H	testplace 2	18-01-2021	100
A	H	testplace 2	18-01-2021	98
B	H	testplace 2	19-01-2021	100
A	H	testplace 2	19-01-2021	97
B	H	testplace 2	20-01-2021	100
A	H	testplace 2	20-01-2021	98

dataline;

data have;
input Method $ 23-24  region$ 23-24 PLACE $ 1-21  DATE :ddmmyy10. value;
format date ddmmyy10.;
datalines;
B	H	testplace 1	11-01-2021	84
A	H	testplace 1	11-01-2021	42
B	H	testplace 1	13-01-2021	93
A	H	testplace 1	13-01-2021	76
B	H	testplace 1	14-01-2021	91
A	H	testplace 1	14-01-2021	81
B	H	testplace 1	15-01-2021	97
A	H	testplace 1	15-01-2021	1
B	H	testplace 1	16-01-2021	96
A	H	testplace 1	16-01-2021	93
B	H	testplace 1	17-01-2021	99
A	H	testplace 1	17-01-2021	97
B	H	testplace 1	18-01-2021	100
A	H	testplace 1	18-01-2021	98
B	H	testplace 1	19-01-2021	100
A	H	testplace 1	19-01-2021	98
B	H	testplace 1	20-01-2021	2
A	H	testplace 1	20-01-2021	98
B	H	testplace 2	11-01-2021	100
A	H	testplace 2	11-01-2021	56
B	H	testplace 2	13-01-2021	100
A	H	testplace 2	13-01-2021	84
B	H	testplace 2	14-01-2021	100
A	H	testplace 2	14-01-2021	87
B	H	testplace 2	15-01-2021	100
A	H	testplace 2	15-01-2021	89
B	H	testplace 2	16-01-2021	100
A	H	testplace 2	16-01-2021	2
B	H	testplace 2	17-01-2021	100
A	H	testplace 2	17-01-2021	97
B	H	testplace 2	18-01-2021	100
A	H	testplace 2	18-01-2021	98
B	H	testplace 2	19-01-2021	100
A	H	testplace 2	19-01-2021	97
B	H	testplace 2	20-01-2021	100
A	H	testplace 2	20-01-2021	98

;

I wish to have a output as a tabulate or freq like this based on the variables in the dataset:

so what i mean is :

testplace 1, method A is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 1, method B is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 2, method A is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 2, method B is 0 days of waiting time as all values are over 10

Can someone please help with a code that can do this calculation?

[ballardw]

PLEASE run that data step as posted.

Then fix it. You have specific columns to read data from but the values are not anywhere near those locations and over lap. Your input statement says to read Method and region from columns 23 to 24, i.e. the exact same values but column 23 is in the middle of the DATE field by column number. Place you are reading at column 1 but the "place" apparently starts at column 7.

input Method $ 23-24  region$ 23-24 PLACE $ 1-21  DATE :ddmmyy10. value;
format date ddmmyy10.;
datalines;
B	H	testplace 1	11-01-2021	84
A	H	testplace 1	11-01-2021	42
B	H	testplace 1	13-01-2021	93

I think you might be missing some information. Your picture has a "1 day" wait if value is "minimum 10". Would 80 imply 2 days? or some other value? If so please provide a rule.

Possibly

proc format ;
value testdays
0 - 9 = '0 Days'
10-high= '1 Day'
;
title;
proc tabulate data=have;
   class date region place method;
   var value;
   table date='Waiting time for ',
         region *Place,
         method *value=''*sum=' '*f=testdays.
         /
   ;
run;

[PhilC]

@ballardw  use min for this, reverse the format values, and remove date='Waiting time for '.

proc format ;
value testdays
0 - 9 = '1 Days'
10-high= '0 Day'
;
title;
proc tabulate data=have;
   class region place method;
   var value;
   table region *Place,
         method *value=''*MIN=' '*f=testdays.
         /
   ;
run;

[mmea]

Sorry. I just copied an old dataline way. The input should just be the variables and the matching format.
So for method $10, region $10, place $50, date ddmmyy10. Value $10

[mmea]

No i meant 10. The numbers are percent. So if a value is below 10% then it means that there is now time avaible that day, if the next day is then over 10% then i counts 1 day. Based on the dates.

Makes sense?

I doesnt mattr what the value is as long as it is over or under 10%

[mmea]

data have;
input Method $ region$  PLACE $  DATE :ddmmyy10. value$
format date ddmmyy10.;
datalines;
B	H	testplace 1	11-01-2021	84
A	H	testplace 1	11-01-2021	42
B	H	testplace 1	13-01-2021	93
A	H	testplace 1	13-01-2021	76
B	H	testplace 1	14-01-2021	91
A	H	testplace 1	14-01-2021	81
B	H	testplace 1	15-01-2021	97
A	H	testplace 1	15-01-2021	1
B	H	testplace 1	16-01-2021	96
A	H	testplace 1	16-01-2021	93
B	H	testplace 1	17-01-2021	99
A	H	testplace 1	17-01-2021	97
B	H	testplace 1	18-01-2021	100
A	H	testplace 1	18-01-2021	98
B	H	testplace 1	19-01-2021	100
A	H	testplace 1	19-01-2021	98
B	H	testplace 1	20-01-2021	2
A	H	testplace 1	20-01-2021	98
B	H	testplace 2	11-01-2021	100
A	H	testplace 2	11-01-2021	56
B	H	testplace 2	13-01-2021	100
A	H	testplace 2	13-01-2021	84
B	H	testplace 2	14-01-2021	100
A	H	testplace 2	14-01-2021	87
B	H	testplace 2	15-01-2021	100
A	H	testplace 2	15-01-2021	89
B	H	testplace 2	16-01-2021	100
A	H	testplace 2	16-01-2021	2
B	H	testplace 2	17-01-2021	100
A	H	testplace 2	17-01-2021	97
B	H	testplace 2	18-01-2021	100
A	H	testplace 2	18-01-2021	98
B	H	testplace 2	19-01-2021	100
A	H	testplace 2	19-01-2021	97
B	H	testplace 2	20-01-2021	100
A	H	testplace 2	20-01-2021	98

;

This i my dataset, i dont no how to do the formats for the variables.

But I want them all.

The rule is "WAITING TIME UNTILL OVER VALUE 10"  so if a testplace1, method A, has values over 10 untill the 15th of January, where the value is under 10, the next day (the 16th) the value is over 10 again - which means testplace 1, method A has a waiting time of 1 day.

If the tesplace has a value over 10 for each day - then there is 0 days of waiting time for this place-

[Shmuel]

You wrote:   

testplace 1, method A is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 1, method B is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 2, method A is 1 day of waiting time  because it only is under 10 for untill the day after

testplace 2, method B is 0 days of waiting time as all values are over 10

Did you mean to check values under 100 or 100 and above - instead 10 ?

Try by creating a new variable:

      if value >= 100 then freq = 0; else freq = 1;

then run proc tabulate and sum freq variable.  

[mmea]

No i meant 10. the values are percentages

If the value is below 10% then the code has to count untill there is over 10% 

So if it is under 10 at testplace 1, method A, 12/01/2021 

and over 10 at testplace 1, method A, 13/01/2021, then it should say 1 day

if it got over 10 at the 14th then it will say 2 days

make sense?