make new variables based on advanced statments

[mmea]

Hi I have this example dataframe;

PLACE	DATE	NUMBER
Toronto	12JAN20201	0
Toronto	13JAN20201	0
Toronto	14JAN20201	99
Toronto	15JAN20201	99

i want a new column in my data called DAYS - is should basically be based on if NUMBER is more than 10 then is shoul count from when it was less until when it became more than 10 from the earliest date - so in this case at TORONTO, 14JAN2021, 99 it would say in the new column 2 days

hope it makes senes

[ballardw]

You may need to provide a more complex example. There are some questions that you need to answer or demonstrate in your data.

1) Do you have more than one value for place and should the process start over with the "earliest date" when a new Place is encountered

2) is your data sorted by Place and date?

3) Does the first record/"earliest date" for Place always have a 0? Always have a value less than 10?

4) Does the value of number decrease and then increase? If the value of number decreases to below 10 might that become an "earliest date"

5) Is your date variable a SAS date value, i.e. a numeric variable with a Date format assigned?

6) What value should days have, and on which records, if Number never goes over 10?

7) Is the value of Days only to appear on one record? More than 1? If more than 1 which records?

[PeterClemmensen]

Do you have more than one plane? Can there be gaps in your data? 

Please be more specific in your requirements and provide exactly what your resulting data should look like

[mmea]

This is a part of my original data

The numbers is the capacity of available time of test

in this case all numbers are over 10 then from theres a waiting time of test at 0 days

PLACE	Method	DATE	NUMBER	new column
T Covid-19 Test place	A	12/01/2021	87	0 days
T Covid-19 Test place	B	12/01/2021	62
T Covid-19 Test place	A	13/01/2021	100
T Covid-19 Test place	B	13/01/2021	77
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

if the case is like this:

PLACE	Method	DATE	NUMBER	new column
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	2 days
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

ithe a case could also be like this:

PLACE	Method	DATE	NUMBER	new column
T Covid-19 Test place	A	12/01/2021	1
T Covid-19 Test place	B	12/01/2021	3
T Covid-19 Test place	A	13/01/2021	78	1 day
T Covid-19 Test place	B	13/01/2021	77
T Covid-19 Test place	A	14/01/2021	188
T Covid-19 Test place	B	14/01/2021	1	1 day
T Covid-19 Test place	A	15/01/2021	1119
T Covid-19 Test place	B	15/01/2021	67

1) There is always one value for each place and it should always count from the earliest date.

2) my data is sorted by date

3)The first record do not always have the value 0  - it was just to illustrate that if theres is a values less than 10 from the earliest date until the 3rd date then the new column will say 2 days - as it took to days for that place to have a value over 10

4)the counting should always start from the earliest date - if the earliest date is over 10 from the beginning then the new column should say 0 days etc.

5) my date format is actually DDMMYY10. 

6) for a place that have under 10 all days then the column should says "NO DAYS AVAILABLE" - if the place has over 10 all days then it will be "0 days"

7) the days should appear every new case as shown in table 3

And if there are gaps or no values then "No days available"

Hope it is understandable

[ballardw]

It appears that METHOD is involved in your calculation. Can you state the rule(s) involved?

[mmea]

There can be different methods for th same place - is should actually also consider that in the calculations - i forgot to mention that.

so the "days" label should be stated for each method 

so if:

PLACE	METHOD	DATE	number	DAYS
T covid-19 test place	A	12/01/2021	12	0 days
T covid-19 test place	B	12/01/2021	67	0 days

i

[PeterClemmensen]

I'm still not quite sure here. You say: "1) There is always one value for each place and it should always count from the earliest date.".

Does that mean you have multiple different values for Place? I.e. Could place both be "T covid-19 test place" and "S covid-19 test place" ?

[mmea]

I have multiple places - and different places - them same place has a method A and B

each place goes from current date and 2 weeks ahead

when a specific place for method A has a number over 10 for all dates  then it means this place has 0 days waiting time

if it has for method B for all dates a number below 10 then this place for method B has no available time.

If the cases is that method A starts with a number under 10 on th 12th and 13th, but then becomes over 10 at the 14th then the places has a waiting time at 2 days

but there are multiple places where the earliest date is the current date for each new place

[mmea]

An no each places has different names 

[PeterClemmensen]

Ok. And why is the new column = 1 in the third-last row of your last example?

I thought number should be > 10 for the new column to have a value?

[mmea]

Sorry typing mistake.. the 1 day should be on the next day when its over 10 🙂 so at number = 67

[mmea]

If a place has over 10 at the 12th, then under 10 at the 13 th, and then over 10 at the 14th and then under 10 for the rest of the days - then the waiting time will 1 day.

The definition is: waitingtime until next day where the number is at least 10

[Kurt_Bremser]

Since you seem to want to treat the methods separately, you need to sort by method before date. Try this:

data have;
infile datalines dlm="," dsd truncover;
input place :$30. method  :$1. date :ddmmyy10. number;
format date yymmdd10.;
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
;

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

data want;
set have;
by place method;
retain s_date;
if first.method then s_date = .;
if s_date = . and number lt 10 then s_date = date;
if s_date ne . and number ge 10
then do;
  days = date - s_date;
  s_date = .;
end;
drop s_date;
run;

Note how source data is supplied in a data step with datalines, so everyone can create the dataset exactly as is with a simple copy/paste and submit. It also leaves no doubts about dataset structure and contents.

[PeterClemmensen]

See if this helps you

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 do;
      new = 0;
      h.replace();
   end;

   else do;
      if n > 10 & number <= 10 then do;
         new = d - date;
         date = d;
         number = n;
         h.replace();
      end;
   end;

   number = n;
   date = d;

   keep PLACE Method DATE NUMBER new;
run;

[PeterClemmensen]

Just made a small correction.

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 new = d - date;
      date = d;
      number = n;
   end;

   h.replace();

   number = n;
   date = d;

   keep PLACE Method DATE NUMBER new;
run;

Result:

PLACE                  Method  DATE        NUMBER  new 
T Covid-19 Test place  A       12/01/2021  7       0 
T Covid-19 Test place  B       12/01/2021  7       0 
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     1 
T Covid-19 Test place  B       14/01/2021  345     1 
T Covid-19 Test place  A       15/01/2021  188     . 
T Covid-19 Test place  B       15/01/2021  67      .