topic Combine multiple rows into single row - concatenate one or two columns in SAS Programming

Combine multiple rows into single row - concatenate one or two columns

Stalk — Thu, 24 Feb 2022 14:59:49 GMT

I have a dataset with 3 rows per id and would like to combine them into one row. Concatenate the equip_id and Parent_EvntID, vnt_name? How can I combine them?

Thank you

pId	date_rec	Equip_Id	Parent_EvntID	vnt_Name	Base_coverage	cov_Pct	Accession_No	Ct	threshold	frames	Alt_Coverage
23	2/15/2022	ABI	45	PUI				22.3
23	2/15/2022	Seq	26	Test	0.93	0.99	MZ4352
23	2/15/2022	Mdx	Null	Null					30	10	99.59

pId	date_rec	Equip_Id	Parent_EvntID	vnt_Name	Base_coverage	cov_Pct	Accession_No	Ct	threshold	frames	Alt_Coverage
23	2/15/2022	ABI_SEQ_Mdx	45_26	PUI_Test	0.93	0.99	MZ4352	22.3	30	10	99.59

Re: Combine multiple rows into single row - concatenate one or two columns

ballardw — Thu, 24 Feb 2022 15:14:10 GMT

If any of the other variables then the ones you explicitly say to combine have 2 or more values what are the rules for selecting the right one? Such as if your Base_coverage variable had values of 0.85, 0,93 and 0.95?

Can you describe exactly how you expect to use that data set? There may be some things you haven't considered.

Such as what if you have another Pld with the exact same values for Equip_id but they occur in another order. What would the difference in meaning be if for different Pld Equip_id is "ABI_SEQ_Mdx" "ABI_Mdx_SEQ" or "SEQ_ABI_Mdx" (with 3 values you have 6 possible permutations so this is just a start)

By NULL do you actually mean a SAS Missing value or do you actually have 'Null' in the data set?

Re: Combine multiple rows into single row - concatenate one or two columns

Stalk — Thu, 24 Feb 2022 17:00:38 GMT

I want to group by PID, no other rules\filters to select the values.
3 records will have different test results( mutually exclusive) that I want to combine into one row. Some times equip_id changes so I want to capture all the 3 equip_id every time to show on what machine these tests were run.