Assigning values based on another variable

[Ihsan-Mahdi]

Hello,

I have a set with hospital IDs, names, and addresses. In the same set the same hospital could be the place for patient admission, patient referral (patient sent to another hospital), or patient reception (patient came from another hospital). The address is assigned only to the admission hospital.

Is there a quick way/code to create referring hospital and receiving hospital address variables  using the hospital ID and Address fields? 

Thank you!

[PaigeMiller]

---

@Ihsan-Mahdi wrote:

Is there a quick way/code to create referring hospital and receiving hospital address variables  using the hospital ID and Address fields? 

---

I think the answer is YES, but without seeing example data and example desired output, I don't think I can write such code. If the information is confidential, you can make up hospital names and addresses, as long as the data set and desired output illustrates the actual problem.

[Ihsan-Mahdi]

Thanks for offering to help! I think the tables below best describe (to me at least 😄) what I have and what I want:

HAVE
Obs	Hospital ID	Hospital Name (where patient currently is)	Address	Referring Hx ID	Receiving Hx ID
1	8	A	310 S X Ave	.	3
2	99	B	31 Y Ave	.	4
3	102	C	45 D Ave	5	.
4	37	D	63 E Ave	6	.

WANT
Obs	Hospital ID	Hospital Name (where patient currently is)	Address	Referring Hx ID	Ref Hx Address	Receiving Hx ID	Rec Hx Address
1	8	A	310 S X Ave	.		3
2	99	B	31 Y Ave	.		4
3	102	C	45 D Ave	5		.
4	37	D	63 E Ave	6		.

[PaigeMiller]

In row 1, the receiving hospital ID is 3. How would we know what the address of hospital 3 is?

[Ihsan-Mahdi]

The Hospital ID is the same for each hospital regardless of it being admitting, referring, or receiving. So, a receiving hospital ID (3) in one record, and a referring hospital ID (3) in another record would be the same hospital but being a different place for a different patient. Each observation is a patient record.

[PaigeMiller]

---

@Ihsan-Mahdi wrote:

The Hospital ID is the same for each hospital regardless of it being admitting, referring, or receiving. So, a receiving hospital ID (3) in one record, and a referring hospital ID (3) in another record would be the same hospital but being a different place for a different patient. Each observation is a patient record.

---

I'm afraid this does not answer my question which was: How would we know what the address of hospital 3 is?

[Patrick]

Below one way how to go about this that avoids creation of additional variables. 

In your sample data you don't provide an address for all hospital IDs and though you will only get formatted values for the IDs where there is somewhere in your data a matching address.

data have;
  infile datalines truncover dlm='|' dsd;
  input obs Hospital_ID Hospital_Name$ Address:$40. Referring_Hx_ID Receiving_Hx_ID;
  datalines;
1|8|A|310 S X Ave|.|3
2|99|B|31 Y Ave|.|102
3|102|C|45 D Ave|5|.
4|37|D|63 E Ave|6|.
;

proc sql;
  create view v_hospital_addresses as
  select distinct
    'hospital_name' as fmtname,
    'n' as type,
    Hospital_ID as start,
    address as label
  from have
  ;
quit;

proc format cntlin=v_hospital_addresses;
run;

proc print data=have;
  format hospital_id Referring_Hx_ID Receiving_Hx_ID hospital_name.;
run;

[Ihsan-Mahdi]

Thank you so much. I will try this and let you know. Really appreciate it!

[Patrick]

---

@Ihsan-Mahdi wrote:
Thank you so much. I will try this and let you know. Really appreciate it!

---

Just as a feedback:

I've spent more than half of my time for converting your table with sample data to a SAS data step have that creates the data. 

If you want to help us help you then going forward please provide sample data via SAS code that creates the data same as I've done it in my data have step.

[Ihsan-Mahdi]

Will do. Sorry for the inconvenience and thanks again.

[Ihsan-Mahdi]

Just wondering, is there a way that involves creating new variables?

[Patrick · Accepted solution]

---

@Ihsan-Mahdi wrote:

Just wondering, is there a way that involves creating new variables?

---

Sure. Once you've got the format created you can use it to populate a new variable in a data step using code like: 

Referring_Hx_addr=put(Referring_Hx_ID, hospital_name.);

[Ihsan-Mahdi]

Excellent! Thank you!

[Patrick]

If you've got in your data the hospital IDs for patient admission, patient referral and patient reception then it's a simple lookup.

To provide code we would need sample data that's representative for your real data (please share via SAS data step code that creates such data).