topic Re: Make an output based on advanced calculations in New SAS User

Make an output based on advanced calculations

mmea — Wed, 13 Jan 2021 15:30:55 GMT

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?

Re: Make an output based on advanced calculations

ballardw — Wed, 13 Jan 2021 16:22:19 GMT

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;

Re: Make an output based on advanced calculations

Shmuel — Wed, 13 Jan 2021 16:30:46 GMT

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.

Re: Make an output based on advanced calculations

PhilC — Wed, 13 Jan 2021 16:38:39 GMT

@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;

Re: Make an output based on advanced calculations

mmea — Wed, 13 Jan 2021 16:36:25 GMT

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

Re: Make an output based on advanced calculations

mmea — Wed, 13 Jan 2021 16:38:12 GMT

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%

Re: Make an output based on advanced calculations

mmea — Wed, 13 Jan 2021 17:01:26 GMT

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?

Re: Make an output based on advanced calculations

mmea — Thu, 14 Jan 2021 08:18:02 GMT

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-