make new variables based on advanced statments

[mmea]

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?

[PeterClemmensen]

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      .   

[mmea]

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?

[PeterClemmensen]

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 🙂

[mmea]

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
;

[PeterClemmensen]

So from this data, your desired result has only 3 observations, right?

[mmea]

yes- from this example data - is the same structure in the real data på I have hundreds places

[mmea]

but*

[PeterClemmensen]

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?

[mmea]

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

[PeterClemmensen · Accepted solution]

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 

[PeterClemmensen]

Also, I don't have more time today. I hope you found your answer. If so, please remember to close the thread 🙂

[mmea]

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

[mmea]

fixed now 🙂

THANKS

[PeterClemmensen]

Anytime 🙂