HELP with conditional statements based on duplicated and nonduplicating variables Let me explain....

[Mdxolly]

I have dataset like this:

Data Facility;

infile datalines delimiter='|';

input FacilityName $ FacilityAddress $ FacilityServices $;

datalines;

Skybuilding1|123 Main St|Cardiology

Skybuilding1|123 Main St|Oncology

Skybuilding2|124 Main St|Primary Care

Skybuilding2|124 Main St|Primary Care

Meadowsbuilding3|125 Main St|Radiology

Meadowsbuilding4|177 Fake St|Dermatology

;

I want to convert that dataset to a new one where  if FacilityName and FacilityAddress are duplicated but the value in FacilityServices is different, then take the two values in FacilityServices and concatenate them into one observation. For example observations 1 and 2 would be reduced to one observation with the FacilityServices variable concatenated with a comma in between: Skybuilding1|123 Main St|Cardiology,Oncology

[ballardw]

Please describe how you think you will use that data set.

The resulting data is hard to work with and if you do not know how many possible values of "FacilityServices" may exist for any one facility you may not declare a length long enough to hold all of the text.

Please note as well that your example code does not read all of the values shown as they are truncated to 8 characters.

[Reeza · Accepted solution]

See two methods here, see the output below. The first table was the sample input, the last two tables are the sample outputs. Either one should work for you.

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

[Mdxolly]

I tried the first method and it worked! Thank you!

[r_behata]

Data Facility;
infile datalines delimiter='|';
input FacilityName :$20. FacilityAddress :$20. FacilityServices :$20.;
datalines;
Skybuilding1|123 Main St|Cardiology
Skybuilding1|123 Main St|Oncology
Skybuilding2|124 Main St|Primary Care
Skybuilding2|124 Main St|Primary Care
Meadowsbuilding3|125 Main St|Radiology
Meadowsbuilding4|177 Fake St|Dermatology
;
run;
proc sort data=Facility;
		by FacilityName FacilityAddress ;
run;

data want(rename=(FacilityServices_=FacilityServices));

length FacilityServices_ $100.;
FacilityServices_='';
do until(last.FacilityAddress);
	set Facility ;
	by FacilityName FacilityAddress ;
	FacilityServices_=catx(',',FacilityServices_,FacilityServices);
end;

drop FacilityServices;
run;