Make a proc tabulate/freq based on calculations

[mmea]

I have this dataset - it is bigger but the structure are similar.

I dont know how to make the formats so jeg just put the dollar sign.

data have;
infile datalines dsd;
input Method :$1. region :$1. PLACE :$12. DATE :ddmmyy10. value;
format date ddmmyy10.;
datalines;
B,H,testplace 1,12-01-2021,84
A,H,testplace 1,12-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,12-01-2021,100
A,H,testplace 2,12-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 this output as a tabulate or freq:

	PLACE	METHOD A	METHOD B
REGION
H	testplace 1	0 day	0 days
	testplace 2	0 days	0 days

so what i mean is :

testplace 1, method A, on date 12/01/2021,should be 0 days of waiting time because as the value is over 10.

testplace 1, method B, on date 12/01/2021,should be 0 days of waiting time because as the value is over 10.

testplace 1, method B, on date 20/01/2021,should be 1 days of waiting time because as the value is under 10 untill next day where it is over 10, if it was under 10 in the next day, and over 10 the day after that, then 2 days of waiting time.

testplace 1, method A, on date 20/01/2021,should be 0 days of waiting time because as the value is over 10.

The values are percentages so anything under 10% there will be a waiting time untill the value is at LEAST 10%

The rule is basicially "WAITING TIME UNTILL next day when the VALUE is over then 10"

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

[Kurt_Bremser]

Whenever you have to deal with blanks in character values, I recommend to use a comma as delimiter. Please check if this code creates your example dataset (your code won't run because of syntax errors, and once those are fixed, deal incorrectly with the blanks in PLACE):

data have;
infile datalines dsd;
input Method :$1. region :$1. PLACE :$12. DATE :ddmmyy10. value;
format date ddmmyy10.;
datalines;
B,H,testplace 1,12-01-2021,84
A,H,testplace 1,12-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,12-01-2021,100
A,H,testplace 2,12-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
;

[mmea]

I changed it in my question - hope that someone can help me

[Kurt_Bremser]

Based on the dataset as posted previously, try this:

proc sort data=have;
by region place method descending date;
run;

data comp;
set have;
by region place method;
retain _end;
format _end ddmmyy10.;
if first.method then _end = date + 1;
if value ge 10
then do;
  _end = date;
  days = 0;
end;
else do;
  days = ifn(_end ne .,_end - date,0);
end;
run;

proc sort data=comp;
by date region place method;
run;

proc transpose
  data=comp
  out=want (drop=_:)
  prefix=days_
;
by date region place;
var days;
id method;
run;

[mmea]

THANK YOU SO MUCH - IT HELPED AND WORKED!

Just one last question before closing this post - how can I make a proc report like this based on the results I got;

I have to run this code for every day - so the proc report/tabulate has to be based on the current dat:

[Kurt_Bremser]

Start with a simple PROC PRINT, and from there expand with TITLEs:

proc format;
picture daysfmt
  low-high = "009 days"
;
run;

%let repdate=%sysfunc(inputn(20210112,yymmdd8.));

proc print data=want noobs;
where date = &repdate;
format method: daysfmt.;
run;

[mmea]

I cant see anything - other than formats?

Should the proc print give me a proc report?

How can I make a proc tabulate like the one I showed in a proc report?

Should I add that under this ?

[Kurt_Bremser]

This is the result of the PROC PRINT:

DATE	region	PLACE	method_A	method_B
12/01/2021	H	testplace 1	0 days	0 days
12/01/2021	H	testplace 2	0 days	0 days

(the header line is shifted because of the tab width of the </> subwindow)

You don't need TABULATE or REPORT, as the data is already contained in dataset WANT.

[mmea]

1086
1087 %let repdate=%sysfunc(inputn(20210112,yymmdd8.));
1088
1089 proc print data=pdf1 noobs;
1090 where date = &repdate;
1091 format method: daysfmt.;
ERROR: You are trying to use the numeric format DAYSFMT with the character variable method in data
set WORK.PDF1.

it gives me this in the Log

[Kurt_Bremser]

With the DATA AS POSTED, the code works. Period.

data have;
infile datalines dsd;
input Method :$1. region :$1. PLACE :$12. DATE :ddmmyy10. value;
format date ddmmyy10.;
datalines;
B,H,testplace 1,12-01-2021,84
A,H,testplace 1,12-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,12-01-2021,100
A,H,testplace 2,12-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
;

proc sort data=have;
by region place method descending date;
run;

data comp;
set have;
by region place method;
retain _end;
format _end ddmmyy10.;
if first.method then _end = date + 1;
if value ge 10
then do;
  _end = date;
  days = 0;
end;
else do;
  days = ifn(_end ne .,_end - date,0);
end;
run;

proc sort data=comp;
by date region place method;
run;

proc transpose
  data=comp
  out=want (drop=_:)
  prefix=method_
;
by date region place;
var days;
id method;
run;

proc format;
picture daysfmt
  low-high = "009 days"
;
run;

%let repdate=%sysfunc(inputn(20210112,yymmdd8.));

proc print data=want noobs;
where date = &repdate;
format method: daysfmt.;
run;

Log:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         data have;
 74         infile datalines dsd;
 75         input Method :$1. region :$1. PLACE :$12. DATE :ddmmyy10. value;
 76         format date ddmmyy10.;
 77         datalines;
 
 NOTE: The data set WORK.HAVE has 36 observations and 5 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 114        ;
 115        
 116        proc sort data=have;
 117        by region place method descending date;
 118        run;
 
 NOTE: There were 36 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.HAVE has 36 observations and 5 variables.
 NOTE:  Verwendet wurde: PROZEDUR SORT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 119        
 120        data comp;
 121        set have;
 122        by region place method;
 123        retain _end;
 124        format _end ddmmyy10.;
 125        if first.method then _end = date + 1;
 126        if value ge 10
 127        then do;
 128          _end = date;
 129          days = 0;
 130        end;
 131        else do;
 132          days = ifn(_end ne .,_end - date,0);
 133        end;
 134        run;
 
 NOTE: There were 36 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.COMP has 36 observations and 7 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 135        
 136        proc sort data=comp;
 137        by date region place method;
 138        run;
 
 NOTE: There were 36 observations read from the data set WORK.COMP.
 NOTE: The data set WORK.COMP has 36 observations and 7 variables.
 NOTE:  Verwendet wurde: PROZEDUR SORT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 139        
 140        proc transpose
 141          data=comp
 142          out=want (drop=_:)
 143          prefix=method_
 144        ;
 145        by date region place;
 146        var days;
 147        id method;
 148        run;
 
 NOTE: There were 36 observations read from the data set WORK.COMP.
 NOTE: The data set WORK.WANT has 18 observations and 5 variables.
 NOTE:  Verwendet wurde: PROZEDUR TRANSPOSE - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 149        
 150        proc format;
 151        picture daysfmt
 152          low-high = "009 days"
 153        ;
 NOTE: Format DAYSFMT is already on the library WORK.FORMATS.
 NOTE: Format DAYSFMT has been output.
 154        run;
 
 NOTE:  Verwendet wurde: PROZEDUR FORMAT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 155        
 156        %let repdate=%sysfunc(inputn(20210112,yymmdd8.));
 157        
 158        proc print data=want noobs;
 159        where date = &repdate;
 160        format method: daysfmt.;
 161        run;
 
 NOTE: There were 2 observations read from the data set WORK.WANT.
       WHERE date=22292;
 NOTE:  Verwendet wurde: PROZEDUR PRINT - (Gesamtverarbeitungszeit):
       real time           0.03 seconds
       cpu time            0.03 seconds
       
 
 162        
 163        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 175        

[mmea]

Is it possible to make the want dataset a proc report, as I need to not show the dates and make a layout of colors

[Kurt_Bremser]

Replace the TRANSPOSE and PRINT with this:

proc report data=comp;
where date = &repdate;
column region place days,method n;
define region / group;
define place / group;
define method / across;
define days / "" display format=daysfmt.;
define n / noprint;
run;

[mmea · Accepted solution]

and what does "009 days" means?

[Kurt_Bremser]

---

@mmea wrote:

and what does "009 days" means?

---

Maxim 1: Read the Documentation, in this case, the PICTURE Statement in PROC FORMAT.