Creating an Indicator using an existing Unique Identifier

[vbylsma]

Good afternoon, 

I am cleaning longitudinal data in a wide format. Each individual in the dataset has 1-5 records and I am implementing some logic steps to determine which 2 records to keep for each person. I could create a dummy variable and label each row 1_2 or 2_4 to indicate which records to keep, then later run a PROC SQL to pull the records, but I am wondering if it's possible to use the unique identifier for each record *as* the dummy variable. Examples below:

Here is what I have that works:
When Vaccine1 = Vaccine2 and days_between2 >= 60 then "1_2"
When Vaccine2 is missing then "1"
Else "F"

BUT is it possible to do something like this?:

When Vaccine1 = Vaccine2 and days_between2 >= 20 then "ImmRecID1_ImmRecID2"
When Vaccine2 is missing then "ImmRecID1"
Else "F"

As written it doesn't work, but any tips to tell SAS "Use the value from 'ImmRecID1' and 'ImmRecID2' here" would be awesome! The customer would prefer it's returned to a long format when we are finished, so I will need a list of all the unique record identifiers in the end. Any other suggestions are also welcome!

[Reeza]

Post some example data and expected output and we can help with the code.
Include data that's representative of your actual data.

[vbylsma]

data have;
      input PtID:10. Vaccine1:$10. ImmRecID1:8. Vaccine2:$10. ImmRecID2:8. days_between:3;
 datalines;
1234567890 Polio 11223344 Shingles 99887766 67
9876543201 Influenza 44556677 MMR 88556699 32
8765432189 Tetanus 66554499 . . 
; 
run;

Of course, the data itself goes from Vaccine1 to Vaccine 5, but for the first part of the logic I'm only looking at vaccines 1 and 2. 

Also, I'm working in EG, so I hope this sample of data will work. If not, let me know and I will try to provide whatever else is needed.

[Reeza]

Expected output given this?

[ballardw · Accepted solution]

---

@vbylsma wrote:

Good afternoon, 

I am cleaning longitudinal data in a wide format. Each individual in the dataset has 1-5 records and I am implementing some logic steps to determine which 2 records to keep for each person. I could create a dummy variable and label each row 1_2 or 2_4 to indicate which records to keep, then later run a PROC SQL to pull the records, but I am wondering if it's possible to use the unique identifier for each record *as* the dummy variable. Examples below:

Here is what I have that works:
When Vaccine1 = Vaccine2 and days_between2 >= 60 then "1_2"
When Vaccine2 is missing then "1"
Else "F"

BUT is it possible to do something like this?:

When Vaccine1 = Vaccine2 and days_between2 >= 20 then "ImmRecID1_ImmRecID2"
When Vaccine2 is missing then "ImmRecID1"
Else "F"

As written it doesn't work, but any tips to tell SAS "Use the value from 'ImmRecID1' and 'ImmRecID2' here" would be awesome! The customer would prefer it's returned to a long format when we are finished, so I will need a list of all the unique record identifiers in the end. Any other suggestions are also welcome!

---

When you provide text in quotes that is the value such as then "ImmRecID1_ImmRecID2"

If you mean to combine the values of variables then you need to use a function that does such.

Possibly

When Vaccine1 = Vaccine2 and days_between2 >= 20 then then catx('_',ImmRecID1,ImmRecID2)
When Vaccine2 is missing then cats(ImmRecID1)
Else "F"

If those variables you reference are numeric then you are subject to SAS implied numeric to character conversion rules.

[vbylsma]

Worked perfectly, thank you! I knew it was able to be done, I just haven't needed to do it before.