Assigning a number to a new variable based on the date value in another column

[rtniblett15]

Novice SAS programmer here.

I have data from an electronic animal feeder that I am trying to set up for analysis. I have a variable "entrydate" which signifies the date of the feeding visit, and I want to create a new variable that refers to the date value and assigns a number to it. For example, the first date is 30AUG22, and I want to create a new variable "dayoftest" which relates to the day of the experiment, where 31AUG22 is 0, 01SEP22 is 1, etc. There are many observations in this dataset where an animal could visit the feeder multiple times, thus creating many instances of the same date. 

I created this bit of code as a cheating workaround, but I want something more legitimate:

data esf6;
set esf4;
by entrydate notsorted;
dayoftest1= (entrydate-22887);
run;

My biggest hang-up is that I do not know how to "set" a value to a certain value based on a value of another variable.

Thanks in advance!

[MarkusWeick]

Hi @rtniblett15,

for me the assigning of values to the new variable "dayoftest1" looks fine.

Do you have any problems with the results? (check für variable types)

"dayoftest" in the text = "dayoftest1" in the code?

Best

Markus

[PaigeMiller]

Looks fine to me, too. What is wrong with the code you show?

Can you please show us a small sample data set, and also the desired output? Data sets should be working SAS data step code (and not Excel files or screen captures) which you can type in yourself, or follow these instructions.

[rtniblett15]

For example, I want a dayoftest variable where 01sep22 is 2, 02sep22 is 3, and so on. I was looking for a way to base the variable off of the value in the entrydate column. The code I generated in my previous post was something that I came up with based on a numerical format and subtracting the different to January 1,1960 since that is the day 0 that SAS uses, but if something happens to this dataset, it could technically be formatted as a date, although I guess this is negligible. I have another question. This dataset is fairly large to my terms (approx 5700 obs) and I want to make a "processed" dataset where wt is averaged without 0's and feed is summed by day to start. There is further processing that needs to be done. I did this by PROC MEANS which accomplished this task, however I wanted to average the feed by day according to the number of animals on a treatment. If I just do an average of feed it bases the average on the feed per animal, so across feeding events per day it could range from .2 to 5 for instance, and the mean is based off that. My overall goal with this dataset is to remove outliers based on predicted values that I calculated from two real weights recorded at the start and end of test; then I want to average the remaining weights by visualid by entrydate and also sum the feed by the same criteria. Here however, the data only shows one visualid, as this trial is over a period of 6 weeks with 19 animals.

Obs desc entry exit location GiltID RFID Trt Duration Feed1 Feed2 Wt Controller Station FV NFV entry2 exit2 feed entrydate12345678910

Gestation	01JAN60:12:26:45	01JAN60:12:26:45	1	220910	9.85E14	AD	873	2.000	0.000	198.414	1	2	1	0	01SEP22:10:33:36	01SEP22:10:48:00	2.000	01SEP2022
Gestation	01JAN60:12:26:46	01JAN60:12:26:46	1	220910	9.85E14	AD	9	0.183	0.000	0.000	1	2	1	0	01SEP22:18:00:00	01SEP22:18:00:00	0.183	01SEP2022
Gestation	01JAN60:12:26:46	01JAN60:12:26:46	1	220910	9.85E14	AD	1230	2.815	0.000	202.382	1	2	1	0	01SEP22:18:43:12	01SEP22:19:12:00	2.815	01SEP2022
Gestation	01JAN60:12:26:46	01JAN60:12:26:46	1	220910	9.85E14	AD	137	0.000	0.000	0.000	1	2	0	1	01SEP22:20:52:48	01SEP22:20:52:48	0.000	01SEP2022
Gestation	01JAN60:12:26:46	01JAN60:12:26:46	1	220910	9.85E14	AD	1774	3.999	0.000	208.776	1	2	1	0	02SEP22:06:28:48	02SEP22:06:57:36	3.999	02SEP2022
Gestation	01JAN60:12:26:47	01JAN60:12:26:47	1	220910	9.85E14	AD	37	0.000	0.000	0.000	1	2	0	1	02SEP22:18:43:12	02SEP22:18:43:12	0.000	02SEP2022
Gestation	01JAN60:12:26:47	01JAN60:12:26:47	1	220910	9.85E14	AD	51	0.000	0.000	0.000	1	2	0	1	02SEP22:19:12:00	02SEP22:19:12:00	0.000	02SEP2022
Gestation	01JAN60:12:26:47	01JAN60:12:26:47	1	220910	9.85E14	AD	143	0.000	0.000	0.000	1	2	0	1	02SEP22:19:55:12	02SEP22:19:55:12	0.000	02SEP2022
Gestation	01JAN60:12:26:47	01JAN60:12:26:47	1	220910	9.85E14	AD	2	0.000	0.000	0.000	1	2	0	1	02SEP22:20:09:36	02SEP22:20:09:36	0.000	02SEP2022
Gestation	01JAN60:12:26:47	01JAN60:12:26:47	1	220910	9.85E14	AD	215	0.366	0.000	210.539	1	2	1	0	03SEP22:04:19:12	03SEP22:04:33:36	0.366	03SEP2022