topic Re: Normalize data based on two variables in SAS Programming

Normalize data based on two variables

rtniblett15 — Tue, 13 Dec 2022 22:48:47 GMT

All,

I would like to "normalize" a rather large dataset where I average bodyweights and feed intake for animals based on the 20 days before they come into estrus. I have attached the csv file that I have exported from SAS that I would like to use. Not sure if I can accomplish this with Proc Stdize or not. Basically for each giltid, I want to take the weight and feed from each day and average that across treatments based on the variable for Estrus2b so that I can make a graph that represents the bodyweight and feed intake for all gilts on a treatment for 20 days before they come in to estrus. For example, 220810 is in estrus beginning on 10/3/22 and I want to take those bodyweights and feed from that day and the additional 20 days prior along with all other gilts in her treatment category to get a treatment average over those 21 days. Let me know if I need to clarify. Thanks!

Re: Normalize data based on two variables

PaigeMiller — Tue, 13 Dec 2022 23:04:48 GMT

Yes, please clarify. You use the word "normalize", does that mean subtract the mean and then divide the result by the standard deviation? How does the second variable get used?

Also, many of us will not download Excel files, as they are a security threat. Some of us (like me) are either unable to download attachments or refuse to download attachments. If you want us to look at your data, show us (a portion of) your data as working SAS Data step code, which you can type yourself, or you can follow these instructions. Do not provide data as attachments or screen captures.

Re: Normalize data based on two variables

rtniblett15 — Tue, 13 Dec 2022 23:14:41 GMT

This is the code that I was trying to use. I can do it using the proc means I am pretty sure. I need to select the observations for each giltid that include the 20 days before each time they come into estrus and average these across treatments.

I will include a portion of the data table.

For example, I need to select the 20 days(obs) before the estrus2b date for this animal and the rest, since estrus is specific to each animal. So it is not actually normalizing but averaging based on a variable in the dataset.

proc means data=esffinalwithestrus chartype;
by trt dayoftest2;
var avgwt totalfeed;
where (entrydate=estrus1b-20);
output out=estrusnormalize mean= avgwt totalfeed;
run;

Obs GiltID entrydate dayoftest2 Trt _TYPE_ _FREQ_ avgwt totalfeed totalfv totalnfv totalvisits block DOB Age Estrus1B Estrus1E AgeatPuberty Estrus1Length Estruswt1 Estrus2b Estrus2e Estrus2Length Estruswt2 Eint cycling estrus3b estrus3e estrus3Length estruswt3 Eint2 e160d e170d e180d e190d e200d123456789101112131415161718192021222324252627282930313233343536373839404142

220810	09SEP2022	1	RS	1	1	193.123	5.4939	1	0	1	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	10SEP2022	2	RS	1	2	.	1.5697	1	1	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	11SEP2022	3	RS	1	2	198.855	5.9987	1	1	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	12SEP2022	4	RS	1	3	197.532	5.9987	3	0	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	13SEP2022	5	RS	1	6	202.052	1.7460	3	3	6	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	14SEP2022	6	RS	1	3	198.634	5.9987	1	2	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	15SEP2022	7	RS	1	8	208.335	5.9987	3	5	8	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	16SEP2022	8	RS	1	13	207.343	5.9987	6	7	13	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	17SEP2022	9	RS	1	6	209.217	5.9987	2	4	6	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	18SEP2022	10	RS	1	11	218.402	5.9987	5	6	11	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	19SEP2022	11	RS	1	2	215.059	5.9987	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	20SEP2022	12	RS	1	9	223.877	5.9987	2	7	9	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	21SEP2022	13	RS	1	8	228.617	5.9987	2	6	8	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	22SEP2022	14	RS	1	7	229.499	5.9987	2	5	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	23SEP2022	15	RS	1	7	230.601	5.9987	2	5	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	24SEP2022	16	RS	1	9	231.483	5.9987	1	8	9	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	25SEP2022	17	RS	1	8	231.703	5.9987	1	7	8	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	26SEP2022	18	RS	1	2	236.443	5.9987	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	27SEP2022	19	RS	1	2	235.010	5.9458	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	28SEP2022	20	RS	1	2	233.136	5.7408	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	29SEP2022	21	RS	1	2	235.341	4.7156	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	30SEP2022	22	RS	1	3	235.672	5.9987	2	1	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	01OCT2022	23	RS	1	9	240.081	5.9987	2	7	9	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	02OCT2022	24	RS	1	4	241.514	5.9987	2	2	4	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	03OCT2022	25	RS	1	8	240.742	5.9987	2	6	8	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	04OCT2022	26	RS	1	2	236.113	5.9987	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	05OCT2022	27	RS	1	2	244.049	5.9987	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	06OCT2022	28	RS	1	2	246.474	5.9987	1	1	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	07OCT2022	29	RS	1	2	252.868	5.9987	2	0	2	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	08OCT2022	30	RS	1	3	247.136	5.9987	3	0	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	09OCT2022	31	RS	1	7	253.749	5.9987	1	6	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	10OCT2022	32	RS	1	7	259.702	5.9987	1	6	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	11OCT2022	33	RS	1	8	.	5.9987	1	7	8	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	12OCT2022	34	RS	1	3	257.938	5.9987	1	2	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	13OCT2022	35	RS	1	7	261.686	5.9987	1	6	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	14OCT2022	36	RS	1	3	261.906	5.9987	1	2	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	15OCT2022	37	RS	1	6	265.875	5.9987	1	5	6	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	16OCT2022	38	RS	1	7	268.741	5.9987	1	6	7	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	17OCT2022	39	RS	1	4	271.607	5.9987	1	3	4	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	18OCT2022	40	RS	1	11	274.693	5.9987	1	10	11	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	19OCT2022	41	RS	1	4	274.693	5.9987	1	3	4	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1
220810	20OCT2022	42	RS	1	3	274.473	5.9987	1	2	3	6	27MAR22	220	12SEP22:20:00:00	13SEP22:09:00:00	170	13.0	198	03OCT22:10:00:00	04OCT22:20:00:00	34.0	241	20.58	1	.	.	.	.	.	1	1	1	1

Re: Normalize data based on two variables

rtniblett15 — Tue, 13 Dec 2022 23:23:35 GMT

As you can see, these weights and corresponding feed intakes are from different timepoints in the test but are representative of the 20 days before each animal comes into estrus, and include the estrus date. These two are on the same treatment and I want to do a proc means for the 20 days before they come into estrus.

	220810	220909		220810	220909
Day	Wt		avg	Feed		avg
1	202.0516	246.6947	224.3732	1.746043	5.998717	3.87238
2	198.6345	249.671	224.1527	5.998717	5.998717	5.998717
3	208.3347	252.2062	230.2705	5.998717	5.998717	5.998717
4	207.3426	253.7495	230.546	5.998717	5.998717	5.998717
5	209.2165	256.1745	232.6955	5.998717	5.998717	5.998717
6	218.4024	258.82	238.6112	5.998717	5.998717	5.998717
7	215.0587	258.82	236.9394	5.998717	5.998717	5.998717
8	223.8771	263.2292	243.5532	5.998717	5.998717	5.998717
9	228.617	263.0088	245.8129	5.998717	5.998717	5.998717
10	229.4989	268.2998	248.8993	5.998717	5.998717	5.998717
11	230.6012	266.0952	248.3482	5.998717	5.998717	5.998717
12	231.483	261.2451	246.3641	5.998717	5.998717	5.998717
13	231.7035	276.3099	254.0067	5.998717	5.998717	5.998717
14	236.4434	273.1499	254.7966	5.998717	5.998717	5.998717
15	235.0104	283.732	259.3712	5.945806	5.998717	5.972261
16	233.1365	276.4568	254.7966	5.740778	5.998717	5.869748
17	235.3411	284.1729	259.757	4.715639	5.998717	5.357178
18	235.6717	276.0159	255.8438	5.998717	5.998717	5.998717
19	240.0809	282.4093	261.2451	5.998717	5.998717	5.998717
20	241.5139	285.9366	263.7253	5.998717	5.998717	5.998717
21	240.7423	285.4957	263.119	5.998717	5.998717	5.998717

Average data based on two variables

PaigeMiller — Wed, 14 Dec 2022 12:37:13 GMT

In my first message, I said:

If you want us to look at your data, show us (a portion of) your data as working SAS Data step code, which you can type yourself, or you can follow these instructions. Do not provide data as attachments or screen captures.

I can't work with the data you provided, it is not in a useable form. Please provide the data in the form requested.

Re: Normalize data based on two variables

mkeintz — Wed, 14 Dec 2022 20:02:30 GMT

@rtniblett15

@PaigeMiller asked you to "show us (a portion of) your data as working SAS Data step code [emphasis mine], which you can type yourself, or you can follow these instructions".

It's possible you will get some helpful answers without doing so, but you're making a tradeoff between what is convenient for you vs what is convenient (and safe) for those who are motivated to provide free help. The more convenient for your correspondents, the more likely you will get meaningful help.

Re: Normalize data based on two variables

ballardw — Wed, 14 Dec 2022 22:53:35 GMT

@rtniblett15 wrote:

As you can see, these weights and corresponding feed intakes are from different timepoints in the test but are representative of the 20 days before each animal comes into estrus, and include the estrus date. These two are on the same treatment and I want to do a proc means for the 20 days before they come into estrus.

220810 220909 220810 220909

Day Wt avg Feed avg

Not quite. For one thing is 220810 supposed to be 22 Aug 2010 (or 1910), 10 Aug 2022, 08 Oct 2022 or some other date? We can't do anything without knowing which dates anything represents. Second Which is the Estrus date? And since you have those as column headings the "date" isn't actually attached to any reading. So "20 days before" is not very clear from that layout.

Re: Normalize data based on two variables

rtniblett15 — Thu, 15 Dec 2022 01:16:51 GMT

Okay, my apologies, as I have never worked with macros. I believe this is what you all are looking for. The table that I added in my last post was to show how I need to average the data. "220810" and "220909" are the first two animals that I included in this code. It is all of the observations for both of these animals during the test period.
When I merged the file with the bodyweight and feed data with the file with the estrus information, it added the estrus dates for every single observation, not just where the entrydate is equal to either of the estrus1b, estrus1e, estrus2b, estrus2e, estrus3b, or estrus3e. While we are looking at this, I may have coded my merge incorrectly, but the estrus information is specific to each animal, not necessarily each entrydate. To summarize, the estrus1b, etc should really only be merged in if it matches with the entrydate that matches. I think this would make the average that I am trying to achieve more attainable.

I could perform this in excel manually, however as you can see, this data is very large and would make the task cumbersome.

data work.ESFFINALWITHESTRUS;
infile datalines dsd truncover;
input GiltID:32. entrydate:DATE9. dayoftest2:32. Trt:$8. _TYPE_:$1. _FREQ_:32. avgwt:32. totalfeed:32. totalfv:32. totalnfv:32. totalvisits:32. block:32. DOB:DATE. Age:32. Estrus1B:DATETIME. Estrus1E:DATETIME. AgeatPuberty:32. Estrus1Length:32. Estruswt1:32. Estrus2b:DATETIME. Estrus2e:DATETIME. Estrus2Length:32. Estruswt2:32. Eint:32. cycling:32. estrus3b:DATETIME. estrus3e:DATETIME. estrus3Length:32. estruswt3:32. Eint2:32. e160d:32. e170d:32. e180d:32. e190d:32. e200d:32.;
format entrydate DATE9. DOB DATE. Estrus1B DATETIME. Estrus1E DATETIME. Estrus2b DATETIME. Estrus2e DATETIME. estrus3b DATETIME. estrus3e DATETIME.;
datalines4;
220810,09SEP2022,1,RS,1,1,193.12296,5.4938632,1,0,1,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,10SEP2022,2,RS,1,2,,1.5696752,1,1,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,11SEP2022,3,RS,1,2,198.85492,5.9987166,1,1,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,12SEP2022,4,RS,1,3,197.53216,5.9987166,3,0,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,13SEP2022,5,RS,1,6,202.05159,1.7460432,3,3,6,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,14SEP2022,6,RS,1,3,198.63446,5.9987166,1,2,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,15SEP2022,7,RS,1,8,208.3347,5.9987166,3,5,8,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,16SEP2022,8,RS,1,13,207.34263,5.9987166,6,7,13,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,17SEP2022,9,RS,1,6,209.21654,5.9987166,2,4,6,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,18SEP2022,10,RS,1,11,218.40237333,5.9987166,5,6,11,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,19SEP2022,11,RS,1,2,215.05873,5.9987166,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,20SEP2022,12,RS,1,9,223.87713,5.9987166,2,7,9,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,21SEP2022,13,RS,1,8,228.61702,5.9987166,2,6,8,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,22SEP2022,14,RS,1,7,229.49886,5.9987166,2,5,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,23SEP2022,15,RS,1,7,230.60116,5.9987166,2,5,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,24SEP2022,16,RS,1,9,231.483,5.9987166,1,8,9,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,25SEP2022,17,RS,1,8,231.70346,5.9987166,1,7,8,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,26SEP2022,18,RS,1,2,236.44335,5.9987166,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,27SEP2022,19,RS,1,2,235.01036,5.9458062,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,28SEP2022,20,RS,1,2,233.13645,5.7407784,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,29SEP2022,21,RS,1,2,235.34105,4.7156394,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,30SEP2022,22,RS,1,3,235.67174,5.9987166,2,1,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,01OCT2022,23,RS,1,9,240.08094,5.9987166,2,7,9,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,02OCT2022,24,RS,1,4,241.51393,5.9987166,2,2,4,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,03OCT2022,25,RS,1,8,240.74232,5.9987166,2,6,8,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,04OCT2022,26,RS,1,2,236.11266,5.9987166,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,05OCT2022,27,RS,1,2,244.04922,5.9987166,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,06OCT2022,28,RS,1,2,246.47428,5.9987166,1,1,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,07OCT2022,29,RS,1,2,252.86762,5.9987166,2,0,2,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,08OCT2022,30,RS,1,3,247.13566,5.9987166,3,0,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,09OCT2022,31,RS,1,7,253.74946,5.9987166,1,6,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,10OCT2022,32,RS,1,7,259.70188,5.9987166,1,6,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,11OCT2022,33,RS,1,8,,5.9987166,1,7,8,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,12OCT2022,34,RS,1,3,257.9382,5.9987166,1,2,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,13OCT2022,35,RS,1,7,261.68602,5.9987166,1,6,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,14OCT2022,36,RS,1,3,261.90648,5.9987166,1,2,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,15OCT2022,37,RS,1,6,265.87476,5.9987166,1,5,6,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,16OCT2022,38,RS,1,7,268.74074,5.9987166,1,6,7,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,17OCT2022,39,RS,1,4,271.60672,5.9987166,1,3,4,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,18OCT2022,40,RS,1,11,274.69316,5.9987166,1,10,11,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,19OCT2022,41,RS,1,4,274.69316,5.9987166,1,3,4,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220810,20OCT2022,42,RS,1,3,274.4727,5.9987166,1,2,3,6,27MAR22,220,12SEP22:20:00:00,13SEP22:09:00:00,170,13,198,03OCT22:10:00:00,04OCT22:20:00:00,34,241,20.58,1,,,,,,0,1,1,1,1
220909,09SEP2022,1,RS,1,2,,3.924188,2,0,2,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,11SEP2022,3,RS,1,4,214.72804,5.9987166,3,1,4,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,12SEP2022,4,RS,1,6,219.90885,5.9987166,5,1,6,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,13SEP2022,5,RS,1,5,219.50467333,5.9987166,2,3,5,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,14SEP2022,6,RS,1,5,223.7669,5.9987166,1,4,5,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,15SEP2022,7,RS,1,5,229.49886,5.9987166,1,4,5,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,16SEP2022,8,RS,1,10,229.05794,5.9987166,3,7,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,17SEP2022,9,RS,1,7,232.36484,0,0,7,7,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,18SEP2022,10,RS,1,8,233.24668,5.9987166,1,7,8,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,19SEP2022,11,RS,1,8,231.92392,5.9987166,1,7,8,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,20SEP2022,12,RS,1,7,240.3014,5.9987166,1,6,7,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,21SEP2022,13,RS,1,3,242.06508,5.9987166,1,2,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,22SEP2022,14,RS,1,3,245.15152,5.9987166,1,2,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,23SEP2022,15,RS,1,4,245.59244,5.9987166,1,3,4,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,24SEP2022,16,RS,1,4,243.38784,5.9987166,1,3,4,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,25SEP2022,17,RS,1,9,245.15152,5.9987166,1,8,9,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,26SEP2022,18,RS,1,9,248.45842,5.9987166,1,8,9,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,27SEP2022,19,RS,1,3,246.69474,5.9987166,1,2,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,28SEP2022,20,RS,1,3,249.67095,5.9987166,2,1,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,29SEP2022,21,RS,1,2,252.20624,5.9987166,2,0,2,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,30SEP2022,22,RS,1,10,253.74946,5.9987166,1,9,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,01OCT2022,23,RS,1,5,256.17452,5.9987166,1,4,5,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,02OCT2022,24,RS,1,3,258.82004,5.9987166,2,1,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,03OCT2022,25,RS,1,13,258.82004,5.9987166,1,12,13,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,04OCT2022,26,RS,1,9,263.22924,5.9987166,1,8,9,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,05OCT2022,27,RS,1,12,263.00878,5.9987166,1,11,12,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,06OCT2022,28,RS,1,10,268.29982,5.9987166,1,9,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,07OCT2022,29,RS,1,10,266.09522,5.9987166,1,9,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,08OCT2022,30,RS,1,1,261.2451,5.9987166,1,0,1,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,09OCT2022,31,RS,1,6,276.30986667,5.9987166,3,3,6,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,10OCT2022,32,RS,1,4,273.14994,5.9987166,1,3,4,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,11OCT2022,33,RS,1,8,283.73202,5.9987166,1,7,8,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,12OCT2022,34,RS,1,6,276.45684,5.9987166,1,5,6,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,13OCT2022,35,RS,1,3,284.17294,5.9987166,2,1,3,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,14OCT2022,36,RS,1,10,276.01592,5.9987166,3,7,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,15OCT2022,37,RS,1,10,282.40926,5.9987166,1,9,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,16OCT2022,38,RS,1,10,285.93662,5.9987166,1,9,10,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,17OCT2022,39,RS,1,8,285.4957,5.9987166,1,7,8,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,18OCT2022,40,RS,1,4,287.81053,5.9987166,2,2,4,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,19OCT2022,41,RS,1,5,285.93662,5.9987166,1,4,5,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
220909,20OCT2022,42,RS,1,12,287.7003,5.9987166,1,11,12,3,30MAR22,217,28SEP22:09:00:00,,182,,250,17OCT22:09:30:00,17OCT22:21:30:00,12,286,19.02,1,,,,,,0,0,0,1,1
;;;;

Re: Normalize data based on two variables

PaigeMiller — Thu, 15 Dec 2022 13:06:17 GMT

Thanks for providing the data in a usable form. (As a process note, we don't need all those variables, just the ones relevant to this problem).

If I am understanding the problem properly, I believe this code solves the problem

proc summary data=esffinalwithestrus(where=(0<=datepart(estrus2b)-entrydate<=20)) nway;
    class giltid;
    var avgwt totalfeed;
    output out=want mean=;
run;

Re: Normalize data based on two variables

rtniblett15 — Thu, 15 Dec 2022 21:22:10 GMT

Yes! Thank you! I wasn't sure of using proc summary. Now I need to add a variable to that dataset which basically labels each day of the estrous cycle for each of these giltid's from -20 to -1 so that I can run a proc means using this variable to since it would repeat for each giltid. I think I could use a do loop, but I have tried doing them before and they never came out correctly. Not sure how I could just create a new variable and code it.

Re: Normalize data based on two variables

PaigeMiller — Thu, 15 Dec 2022 21:51:49 GMT

Why add a variable? Why not use the variable DAYOFTEST?

But even if you do need to add a variable, there's no do loop, there's just an assignment statement.

Re: Normalize data based on two variables

rtniblett15 — Thu, 15 Dec 2022 22:42:35 GMT

I could use DAYOFTEST, however the range of numbers for each giltid would be different because they likely came into estrus at different times. So if I included DAYOFTEST, I do not think the graph would be formatted correctly since the day of test for each animal in this dataset would vary slightly.

Re: Normalize data based on two variables

PaigeMiller — Thu, 15 Dec 2022 22:45:48 GMT

So what is the formula to compute the day of cycle IN WORDS?

Can you write the formula as SAS code?

Re: Normalize data based on two variables

rtniblett15 — Thu, 15 Dec 2022 23:42:06 GMT

The code is as follows.

data esfnormal;

set esfnormal;

dayofestrous=-20;

run;

This does not get what I want, but I understand that it is because I am missing coding. The dataset in this section of code is the result of the proc summary code you formulated. I just want it to repeat from -20 to 0 for each giltid since it is the same number of days for each giltid even though the actual dates are different. I also would like to be able to add code for the original dataset that I gave you data step code for, where it formulates this variable before I do the proc summary statement so that I would not have to do it after. This would be based on the estrus2b variable.

Re: Normalize data based on two variables

rtniblett15 — Thu, 15 Dec 2022 23:59:34 GMT

data esfnormal;
set esfnormal;
by giltid;
if first.giltid then dayofestrous= -20;
else dayofestrous+1;
run;

This code worked. I tried it quite a few times before sorting the data first. Before sorting the data it would give the first value of -20 and then follow to repeat -19 through the rest of the obs for each animal. Each time I ran it it would change from -19 to -18 and so on. So I guess the trick here is to sort before making this variable. However, how would I be able to make this variable prior to making this esfnormal dataset. e.g. the esffinalwithestrus dataset?

I would want to create the same variable, but I would want it to be based on the estrus1b and estrus2b variables. The reason for this is that the first estrous cycle may 21 days, while the next cycle could be in a potential range of 18-22 days or more. I already cannot thank you enough for assisting me. You have been such a great help and valuable resource.

Re: Normalize data based on two variables

PaigeMiller — Fri, 16 Dec 2022 11:17:25 GMT

@rtniblett15 wrote:

However, how would I be able to make this variable prior to making this esfnormal dataset. e.g. the esffinalwithestrus dataset?

I would want to create the same variable, but I would want it to be based on the estrus1b and estrus2b variables.

Again, I ask you to tell me in words how you would do this with the esffinalwithestrus data set. What is the calculation?

Re: Normalize data based on two variables

rtniblett15 — Mon, 30 Jan 2023 20:52:53 GMT

In words, it would be when the estrus1b, estrus2b, or estrus3b variables are equal to the entrydate, that observation would be day 0. Any entrydate before estrus1b would be 20, 19, 18 and so on. Any entrydate after estrus1b and before estrus2b would be 1, 2, 3, 4 and so on. Any entrydate after estrus2b and before estrus3b would be 1, 2, 3, 4 and so on. Any entrydate after estrus3b would be 1, 2, 3, 4 and so on.