topic Re: make new variables based on advanced statments in New SAS User

make new variables based on advanced statments

mmea — Tue, 12 Jan 2021 14:44:52 GMT

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

Re: make new variables based on advanced statments

ballardw — Tue, 12 Jan 2021 14:55:58 GMT

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?

Re: make new variables based on advanced statments

PeterClemmensen — Tue, 12 Jan 2021 14:57:21 GMT

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

Re: make new variables based on advanced statments

mmea — Tue, 12 Jan 2021 15:24:17 GMT

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

Re: make new variables based on advanced statments

ballardw — Tue, 12 Jan 2021 15:33:38 GMT

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

Re: make new variables based on advanced statments

mmea — Tue, 12 Jan 2021 15:39:39 GMT

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

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 08:13:54 GMT

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" ?

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 08:23:13 GMT

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

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 08:24:02 GMT

An no each places has different names

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 09:05:01 GMT

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?

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 09:06:57 GMT

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

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 09:10:46 GMT

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

Re: make new variables based on advanced statments

Kurt_Bremser — Wed, 13 Jan 2021 09:30:41 GMT

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.

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 09:39:58 GMT

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;

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 10:15:19 GMT

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      .

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 12:50:00 GMT

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?

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 13:12:56 GMT

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      .

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 13:24:35 GMT

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?

Re: make new variables based on advanced statments

PeterClemmensen — Wed, 13 Jan 2021 13:34:35 GMT

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 🙂

Re: make new variables based on advanced statments

mmea — Wed, 13 Jan 2021 13:50:12 GMT

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 ;