topic Adding by row with specific variables to add in New SAS User

Adding by row with specific variables to add

GS2 — Thu, 26 Dec 2019 15:35:56 GMT

Using SAS 9.4

I have a data set with measurements at different time points

Pre

2wk

6wk

Within each time point their are two groups of measurements SL and CL and in each group their are 20 measurements.

So time point Pre has 20 SL measurements and 20 CL measurements- 40 total measurements (the same is true of 2wk and 6wk)

Now, each of these measurements does not have data in, ie some are blank.

I need to have the same number of measurements in Pre SL/CL as 2wk SL/CL as 6wk SL/CL by each individual row in the data set.

Example, row 1 has Pre SL- 12 measurements Pre-CL 10 Measurements, 2wk-SL- 14 measurements 2wk CL-12 Measurements, 6wk-SL 14 measuremnts, 6wk-CL 12 measurements

I need the lowest number of measurements of each group (SL and CL) to be added together for this row. From the example above SL-12 measurements and CL-10 measurements added together to get a total SL and a total CL for all 3 time points.

Where I am struggling is each row will be different as to the number of measurements that are completed in the SL and CL groups but I still need the lowest number of measurements per group (SL and CL) per row.

If this does not make sense I can attempt to explain differently but any thoughts on how to go about this would be greatly appreciated. Thank you

Re: Adding by row with specific variables to add

SuryaKiran — Thu, 26 Dec 2019 15:49:14 GMT

Hello,

Please put some effort to create a sample data you have and your desired output from the description you stated so it makes easy for us to understand and fast responses.

Please provide sample data.

Re: Adding by row with specific variables to add

GS2 — Thu, 26 Dec 2019 16:05:20 GMT

sl_mcpjoints

sl_thump_mcp

sl_wrist

sl_wrist_4cm

sl_wrist_8cm

sl_wrist_12cm

sl_wrist_16cm

sl_wrist_20cm

sl_wrist_24cm

sl_wrist_28cm

sl_wrist_32cm

sl_elbow_crease

sl_elbow_4cm

sl_elbow_8cm

sl_elbow_12cm

sl_elbow_16cm

sl_elbow_20cm

sl_elbow_24cm

sl_elbow_28cm

sl_elbow_32cm

18.5

18.8

15.2

14.8

16.7

20.5

22.9

24.2

23.6

26.6

29.4

30.7

cl_mcpjoints

cl_thump_mcp

cl_wrist

cl_wrist_4cm

cl_wrist_8cm

cl_wrist_12cm

cl_wrist_16cm

cl_wrist_20cm

cl_wrist_24cm

cl_wrist_28cm

cl_wrist_32cm

cl_elbow_crease

cl_elbow_4cm

cl_elbow_8cm

cl_elbow_12cm

cl_elbow_16cm

cl_elbow_20cm

cl_elbow_24cm

cl_elbow_28cm

cl_elbow_32cm

17.6

15.3

17.2

20.2

22.9

23.9

24.3

26.5

29.3

30.8

p_sl_mcpjoints_2wk

p_sl_thump_mcp_2wk

p_sl_wrist_2wk

p_sl_wrist_4cm_2wk

p_sl_wrist_8cm_2wk

p_sl_wrist_12cm_2wk

p_sl_wrist_16cm_2wk

p_sl_wrist_20cm_2wk

p_sl_wrist_24cm_2wk

p_sl_wrist_28cm_2wk

p_sl_wrist_32cm_2wk

p_sl_elbow_crease_2wk

p_sl_elbow_4cm_2wk

p_sl_elbow_8cm_2wk

p_sl_elbow_12cm_2wk

p_sl_elbow_16cm_2wk

p_sl_elbow_20cm_2wk

p_sl_elbow_24cm_2wk

p_sl_elbow_28cm_2wk

p_sl_elbow_32cm_2wk

19.2

19.7

15.5

15.1

20.1

23.3

23.5

26.5

29.3

30.8

p_cl_mcpjoints_2wk

p_cl_thump_mcp_2wk

p_cl_wrist_2wk

p_cl_wrist_4cm_2wk

p_cl_wrist_8cm_2wk

p_cl_wrist_12cm_2wk

p_cl_wrist_16cm_2wk

p_cl_wrist_20cm_2wk

p_cl_wrist_24cm_2wk

p_cl_wrist_28cm_2wk

p_cl_wrist_32cm_2wk

p_cl_elbow_crease_2wk

p_cl_elbow_4cm_2wk

p_cl_elbow_8cm_2wk

p_cl_elbow_12cm_2wk

p_cl_elbow_16cm_2wk

p_cl_elbow_20cm_2wk

p_cl_elbow_24cm_2wk

p_cl_elbow_28cm_2wk

p_cl_elbow_32cm_2wk

17.6

18.6

15.1

17.3

20.7

23.8

26.3

29.8

p_sl_mcpjoints_6wk

p_sl_thump_mcp_6wk

p_sl_wrist_6wk

p_sl_wrist_4cm_6wk

p_sl_wrist_8cm_6wk

p_sl_wrist_12cm_6wk

p_sl_wrist_16cm_6wk

p_sl_wrist_20cm_6wk

p_sl_wrist_24cm_6wk

p_sl_wrist_28cm_6wk

p_sl_wrist_32cm_6wk

p_sl_elbow_crease_6wk

p_sl_elbow_4cm_6wk

p_sl_elbow_8cm_6wk

p_sl_elbow_12cm_6wk

p_sl_elbow_16cm_6wk

p_sl_elbow_20cm_6wk

p_sl_elbow_24cm_6wk

p_sl_elbow_28cm_6wk

p_sl_elbow_32cm_6wk

18.9

15.3

15.2

16.7

19.9

22.7

26.1

29.9

p_cl_mcpjoints_6wk

p_cl_thump_mcp_6wk

p_cl_wrist_6wk

p_cl_wrist_4cm_6wk

p_cl_wrist_8cm_6wk

p_cl_wrist_12cm_6wk

p_cl_wrist_16cm_6wk

p_cl_wrist_20cm_6wk

p_cl_wrist_24cm_6wk

p_cl_wrist_28cm_6wk

p_cl_wrist_32cm_6wk

p_cl_elbow_crease_6wk

p_cl_elbow_4cm_6wk

p_cl_elbow_8cm_6wk

p_cl_elbow_12cm_6wk

p_cl_elbow_16cm_6wk

p_cl_elbow_20cm_6wk

p_cl_elbow_24cm_6wk

p_cl_elbow_28cm_6wk

p_cl_elbow_32cm_6wk

18.3

14.8

15.3

17.5

20.7

22.8

23.8

26.6

29.4

29.5

This is what the data looks like but in a wide format. This one isn't a great example because the numbers line up but you can count that there are 12 measurements in each of the 6 rows (put into rows to show they are their own group). I need to add each one to create a total for that row. Where this becomes more difficult is not all of the rows will have the same number of measurements (ie the SL at pre could have 12 measurements, at 2wk have 10 measurements and at 6wk have 12 measurement; in this case I would want to add only 10 measurements for pre/2wk/6wk and not include measurements 11 and 12 from the pre and 6 wk time points). Does this help to clarify the question?

Re: Adding by row with specific variables to add

mkeintz — Thu, 26 Dec 2019 20:50:37 GMT

By "lowest number of measurements" do you want the lowest count of valid (non-missing) measures, or do you want the number of measures that are valid in all three time points?
Do you have an ID variable, such that each ID has three time-points (i.e. 3 observations) with sets of measurements? If so, are you looking for "lowest number of measurements " for each ID, or for the entire sample?
What do your want the results to look like? Presumably your starting data set has 42 variables (ID plus time-of-measures plus two sets of 20 measurement variables), and 3 observations per ID. What does the resulting data set look like, or the resulting report?

Re: Adding by row with specific variables to add

GS2 — Thu, 26 Dec 2019 20:56:54 GMT

1. I want a count at each time point of the non-missing measurements

2. I have an ID for each row so each ID has 3 sets of measurements

3. The finish product will be to add the non-missing values together to get a summation but it needs to be the same count of measurements that go into the summation (meaning Pre SL/CL count cannot be 12 measurements and 2 wk be so other number besides 12). I need the same count of measurements for each group and time point to be added together

Re: Adding by row with specific variables to add

mkeintz — Fri, 27 Dec 2019 00:24:36 GMT

I asked:

By "lowest number of measurements" do you want the lowest count of valid (non-missing) measures, or do you want the number of measures that are valid in all three time points?

@GS2 wrote:

1. I want a count at each time point of the non-missing measurements

I don't see how this answers my question. (Admittedly I should have asked "or do you want the subset of measures that are valid in all three time points?").
Do you have an ID variable, such that each ID has three time-points (i.e. 3 observations) with sets of measurements? If so, are you looking for "lowest number of measurements " for each ID, or for the entire sample?

@GS2 wrote:
2. I have an ID for each row so each ID has 3 sets of measurements

This answer the first part, but crucially not the second part.
What do your want the results to look like? Presumably your starting data set has 42 variables (ID plus time-of-measures plus two sets of 20 measurement variables), and 3 observations per ID. What does the resulting data set look like, or the resulting report?

@GS2 wrote:

3. The finish product will be to add the non-missing values together to get a summation but it needs to be the same count of measurements that go into the summation (meaning Pre SL/CL count cannot be 12 measurements and 2 wk be so other number besides 12). I need the same count of measurements for each group and time point to be added together

OK. I think ;you want the process to be done for each ID independently rather than for the whole sample.

Now, ala your example, if for a given ID, Pre SL/CL and 6WK each have 12 measurements, and 2WK has 6 measurements, are you saying to ignore that ID? Or if that ID is not to be ignored, then presumably you want the sum of 6 values for each time point. Which 6 of the 12 values do you want to use? After all, 6 is the lowest number of measurements.

This is why it would be good if you had more comprehensively provided sample data, in the form of a data step, in response to @SuryaKiran's request. That will help us help you.

Re: Adding by row with specific variables to add

GS2 — Fri, 27 Dec 2019 14:11:32 GMT

I asked:

By "lowest number of measurements" do you want the lowest count of valid (non-missing) measures, or do you want the number of measures that are valid in all three time points?
@GS2 wrote:
1. I want a count at each time point of the non-missing measurements
I don't see how this answers my question. (Admittedly I should have asked "or do you want the subset of measures that are valid in all three time points?").

Response: I want to know what the lowest number of measurements is at each time point and by group (SL/CL) from there I want to be able to sum the lowest count of measurements for each ID (ie row). There will be some IDs with different 'lowest number of measurements'
Do you have an ID variable, such that each ID has three time-points (i.e. 3 observations) with sets of measurements? If so, are you looking for "lowest number of measurements " for each ID, or for the entire sample?

@GS2 wrote:
2. I have an ID for each row so each ID has 3 sets of measurements
This answer the first part, but crucially not the second part.

Response: The lowest number of measurements for each ID (ie row) is what I am after and then how to add the different number of measurements together by ID
What do your want the results to look like? Presumably your starting data set has 42 variables (ID plus time-of-measures plus two sets of 20 measurement variables), and 3 observations per ID. What does the resulting data set look like, or the resulting report?
@GS2 wrote:
3. The finish product will be to add the non-missing values together to get a summation but it needs to be the same count of measurements that go into the summation (meaning Pre SL/CL count cannot be 12 measurements and 2 wk be so other number besides 12). I need the same count of measurements for each group and time point to be added together

OK. I think ;you want the process to be done for each ID independently rather than for the whole sample.

Response: Yes, from the example I want the 6 measurements for each timepoint (pre/2wk/6wk) and group (SL/CL). I would want the first 6 measurements, the way the data is ordered you would not have measurement 2 without first having measurement 1 and so on.

This is why it would be good if you had more comprehensively provided sample data, in the form of a data step, in response to @SuryaKiran's request. That will help us help you.

Response: I did the best I could but companies do have restriction so I provided as best as I am allowed. Thank you for any help!