Hi it works - but dosent give the output I want
For every value under 10 - there should be a text "No available time" - so for the first 4 columns it should say that.
for the 5th column there should be "2 days" as it is now the 14th and you count from the earliest date which is the 12th untill the number is over 10 - which is 2 days?
make sense?
How about this?
data have;
input PLACE $ 1-21 Method $ 23-24 DATE :ddmmyy10. NUMBER;
format date ddmmyy10.;
datalines;
T Covid-19 Test place A 12/01/2021 7
T Covid-19 Test place B 12/01/2021 7
T Covid-19 Test place A 13/01/2021 8
T Covid-19 Test place B 13/01/2021 1
T Covid-19 Test place A 14/01/2021 188
T Covid-19 Test place B 14/01/2021 345
T Covid-19 Test place A 15/01/2021 188
T Covid-19 Test place B 15/01/2021 67
;
data want;
if _N_ = 1 then do;
dcl hash h();
h.definekey('place', 'method');
h.definedata('number', 'date');
h.definedone();
end;
set have;
n = number;
d = date;
if h.find() ne 0 then new = 0;
else do;
if n > 10 & number <= 10 then do;
new = d - date;
date = d;
number = n;
end;
end;
h.replace();
number = n;
date = d;
if number < 10 then text = "No available time";
if new > 0 then text = cat(new, " days");
keep PLACE Method DATE NUMBER new text;
run;
Result:
PLACE Method DATE NUMBER new text T Covid-19 Test place A 12/01/2021 7 0 No available time T Covid-19 Test place B 12/01/2021 7 0 No available time T Covid-19 Test place A 13/01/2021 8 . No available time T Covid-19 Test place B 13/01/2021 1 . No available time T Covid-19 Test place A 14/01/2021 188 2 2 days T Covid-19 Test place B 14/01/2021 345 2 2 days T Covid-19 Test place A 15/01/2021 188 . T Covid-19 Test place B 15/01/2021 67 .
The text work - still do not come with the wished output:
all the numbers that are over 10 should in the text column say = "0 days"
when a number is below 10 then it says "No available time" which is correct, but then it should put "1 day" after as the number has increased to over 10.
So the code should count from when it is below 10 untill it is at least 10 and up then it should says how many days
makes sense?
Please post your desired result exactly as you want it given the sample data from my program.
Please be specific and don't make us guess 🙂
Sorry it just messes up my mind that i cant solve this...
Here is what I wish to have as output
with data, bigger, but similar structure like this:
data have;
input PLACE $ 1-21 Method $ 23-24 DATE :ddmmyy10. NUMBER;
format date ddmmyy10.;
datalines;
PLACE A A 12/01/2021 712
PLACE A B 12/01/2021 712
PLACE A A 13/01/2021 713
PLACE A B 13/01/2021 73
PLACE A A 14/01/2021 75
PLACE A B 14/01/2021 7
PLACE A A 15/01/2021 75
PLACE A B 15/01/2021 112
PLACE B A 12/01/2021 8
PLACE B B 12/01/2021 16
PLACE B A 13/01/2021 85
PLACE B B 13/01/2021 15
PLACE B A 14/01/2021 85
PLACE B B 14/01/2021 1
PLACE B A 15/01/2021 86
PLACE B B 15/01/2021 16
PLACE C A 12/01/2021 188
PLACE C B 12/01/2021 345
PLACE C A 13/01/2021 188
PLACE C B 13/01/2021 67
PLACE C A 14/01/2021 188
PLACE C B 14/01/2021 67
PLACE C A 15/01/2021 188
PLACE C B 15/01/2021 67
;
So from this data, your desired result has only 3 observations, right?
yes- from this example data - is the same structure in the real data på I have hundreds places
but*
So why is Method_A = "4 days" for Place A here? It is not 4 days since Number went from <10 to >=10 as is your reqirement? It is 1 day ago in this example?
You right - I just made a quick example out out - sorry here is the ouput based on the example data.
PLACE | METHOD A | METHOD B |
PLACE A | 0 days | 1 day |
PLACE B | 1 day | 1 day |
PLACE C | 0 days | 0 days |
Place A, method A is 0 days because they are all over 10
place A, method B is 1 days because the patients has to wait 1 day untill over 10
place c, method A and B are both as they are both over 10
Ok. This gives you what you want.
data have;
input PLACE $ 1-7 Method $ 10-11 DATE :ddmmyy10. NUMBER;
format date ddmmyy10.;
datalines;
PLACE A A 12/01/2021 712
PLACE A B 12/01/2021 712
PLACE A A 13/01/2021 713
PLACE A B 13/01/2021 73
PLACE A A 14/01/2021 75
PLACE A B 14/01/2021 7
PLACE A A 15/01/2021 75
PLACE A B 15/01/2021 112
PLACE B A 12/01/2021 8
PLACE B B 12/01/2021 16
PLACE B A 13/01/2021 85
PLACE B B 13/01/2021 15
PLACE B A 14/01/2021 85
PLACE B B 14/01/2021 1
PLACE B A 15/01/2021 86
PLACE B B 15/01/2021 16
PLACE C A 12/01/2021 188
PLACE C B 12/01/2021 345
PLACE C A 13/01/2021 188
PLACE C B 13/01/2021 67
PLACE C A 14/01/2021 188
PLACE C B 14/01/2021 67
PLACE C A 15/01/2021 188
PLACE C B 15/01/2021 67
;
data want;
if _N_ = 1 then do;
dcl hash h();
h.definekey('place', 'method');
h.definedata('number', 'date');
h.definedone();
end;
set have;
by place;
if first.place then do;
a = 0; b = 0;
end;
n = number;
d = date;
if h.find() ne 0 then new = 0;
else do;
if n > 10 & number <= 10 then new = d - date;
date = d;
number = n;
end;
h.replace();
number = n;
date = d;
if method = "A" then a = max(a, new);
if method = "B" then b = max(b, new);
if last.place;
retain a b;
keep place a b;
run;
Result:
PLACE a b PLACE A 0 1 PLACE B 1 1 PLACE C 0 0
Also, I don't have more time today. I hope you found your answer. If so, please remember to close the thread 🙂
Thank you for your time.
it gave me this error tho
ERROR: BY variables are not properly sorted on data set WORK.data.
i will remember
fixed now 🙂
THANKS
Anytime 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.