Retaining the BASEC value from first record of a subject to all the records of the subject per param

[sandhya88]

Hi here is the example of the data I have

Data have;
Input $ID $test visitnum $basefl $result adt randdt

1 CHOL 1 Y HIGH 17JUN2018 18JUN2018
1 CHOL 1 Y LOW 17JUN2018 18JUN2018
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018
1 CHOL 2 “” LOW 24JUN2018
18JUN2018;
run;

I want the basec to be populated as follows:

Data have;
Input $ID $test visitnum $basefl $result adt randdt $basec

1 CHOL 1 Y HIGH 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y LOW 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018 HIGH
1 CHOL 2 “” LOW 24JUN2018
18JUN2018 HIGH;
run;

Logic that I want here is that:
1. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are equally apart from randdt then consider worst case (HIGH) as BASEC and retain the value across the ID.
2. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are not equally apart from RANDDT then choose the BASEC from the closest record to RANDDT and retain its value across the ID.

Please help

Thanks

[ballardw]

Define what "equally apart" in terms of the variables shown means. "apart" from what needs to be part of the definition.

What is that “” supposed to indicate?

Please try to make sure that a data step will run and produce data similar to your actual use. I think that you meant something more like:

 Data have;
Input ID $ test $ visitnum basefl $ result $ adt :date9. randdt :date9.;
format adt randdt date9.;
datalines;
1 CHOL 1 Y HIGH 17JUN2018 18JUN2018
1 CHOL 1 Y LOW 17JUN2018 18JUN2018
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018
1 CHOL 2 . LOW 24JUN2018 18JUN2018
;
run;

Note that the $ goes after the variable name to indicate that the variable is character. Your date variables need to be read with a date informat to have any change of determining any reasonable definition of "equally apart". Note that with inline data, you need to tell SAS where the data starts with a Datalines or Cards statement. The end of data semicolon must be on its own line, otherwise data on the line with the semicolon is ignored. To indicate a missing value, even for character data, you can use a period. The quotes would yield something quite different.

Also, it is best to paste code into a text box opened on the forum with the </> icon above the message window as the main message windows will reformat pasted text and can result in data step code that may not run. Plus the box helps delineate the code from discussion.

If you data is supposed to be significantly different than the above data step creates then show use in some more detail.

---

@sandhya88 wrote:
Hi here is the example of the data I have

Data have;
Input $ID $test visitnum $basefl $result adt randdt

1 CHOL 1 Y HIGH 17JUN2018 18JUN2018
1 CHOL 1 Y LOW 17JUN2018 18JUN2018
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018
1 CHOL 2 “” LOW 24JUN2018
18JUN2018;
run;

I want the basec to be populated as follows:

Data have;
Input $ID $test visitnum $basefl $result adt randdt $basec

1 CHOL 1 Y HIGH 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y LOW 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018 HIGH
1 CHOL 2 “” LOW 24JUN2018
18JUN2018 HIGH;
run;

Logic that I want here is that:
1. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are equally apart from randdt then consider worst case (HIGH) as BASEC and retain the value across the ID.
2. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are not equally apart from RANDDT then choose the BASEC from the closest record to RANDDT and retain its value across the ID.

Please help

Thanks

---

[sandhya88]

Data want;
Input ID $ test$ visitnum basefl$ result$ adt randdt basec$

1 CHOL 1 Y HIGH 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y LOW 17JUN2018 18JUN2018 HIGH
1 CHOL 1 Y HIGH 18JUN2018 18JUN2018 HIGH
1 CHOL 2 . LOW 24JUN2018 18JUN2018 HIGH;
run;

@ballardw 

Thanks for your response and apologies for so many confusions.

Here below is what i meant regarding equally apart and the data is how you have shown in your answer and here above is the data look that i want finally.

when I meant equally apart i mean to say that when we calculate the difference between adt and randdt if we get the same value for any two records.

Logic that I want here is that:
1. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are equally apart (difference between adt and randdt is same )from randdt then consider worst case (HIGH) as BASEC and retain the value across the ID.
2. If a ID is having 2 or more records with BASEFL eq ‘Y’ and are not equally apart from RANDDT then choose the BASEC from the closest record to RANDDT and retain its value across the ID.

TIA again