topic Re: Rolling up Values and concatenate with unique description in SAS Programming

Rolling up Values and concatenate with unique description

bknitch — Tue, 05 May 2020 13:27:12 GMT

I have a data set of about 5k records that I need to roll-up values on and concatenate previous names and ID's to unique fields. Here is a sample of the data I have.

Contract_ID	P_ID	YEAR	NAME	PREVIOUS_P_ID	PREVIOUS_NAME	DESC	H	P	PACE	D	C	M	F	OS	SN	DE
HMA_ABC	001	2016	HMA ABC-001	001	HMA ABC-001	Consolidated	1	0	0	0	0	0	0	0	0	0
HMA_ABC	001	2016	HMA ABC-001	002	HMA ABC-002	Consolidated	1	0	0	0	0	0	0	0	0	0
HMA_ABC	001	2016	HMA ABC-001	003	HMA ABC-003	Consolidated	1	0	0	0	0	0	0	0	0	0

I need to roll-up by the Contract_ID and P_ID. I'd like to concatenate the PREVIOUS_P_ID and PREVIOUS_NAME so that they roll-up to one field. It is also important to note that this only applies to fields with a DESC = 'Consolidated'. Here is an example of what I want my end results to look like

Contract_ID

P_ID

YEAR

NAME

PREVIOUS_P_ID

PREVIOUS_NAME

DESC

PACE

HMA_ABC

001

2016

HMA ABC-001

001,002,003

HMA ABC-001,HMA ABC-002, HMA ABC-003

Consolidated

Re: Rolling up Values and concatenate with unique description

ed_sas_member — Tue, 05 May 2020 14:12:15 GMT

Hi @bknitch

Here is an attempt to achieve this.

I have added a fourth row to row data to have a case where DESC is not equal to "Consolidated".

Is the output correct in this case?

data have;
	infile datalines dlm="09"x;
	input Contract_ID $ P_ID $ YEAR NAME:$20. PREVIOUS_P_ID $ PREVIOUS_NAME:$20. DESC:$20. H	P	PACE	D	C	M	F	OS	SN	DE;
	datalines;
HMA_ABC	001	2016	HMA ABC-001	001	HMA ABC-001	Consolidated 	1	0	0	0	0	0	0	0	0	0
HMA_ABC	001	2016	HMA ABC-001	002	HMA ABC-002	Consolidated 	1	0	0	0	0	0	0	0	0	0
HMA_ABC	001	2016	HMA ABC-001	004	HMA ABC-004	Not consolidated 	1	0	0	0	0	0	0	0	0	0
HMA_ABC	001	2016	HMA ABC-001	003	HMA ABC-003	Consolidated 	1	0	0	0	0	0	0	0	0	0
	;
run;

proc sort data=have out=have_sorted;
	by Contract_ID P_ID DESC YEAR NAME H P	PACE D	C	M	F	OS	SN	DE;
run;

proc transpose data=have_sorted out=have_tr1 (drop=_name_);
	var PREVIOUS_P_ID ;
	by Contract_ID P_ID DESC YEAR NAME H P	PACE D	C	M	F	OS	SN	DE;
run;

data want1;
	set have_tr1;
	length PREVIOUS_P_ID $100.;
	PREVIOUS_P_ID = catx(", ", of col:);
	drop col:;
run;

proc transpose data=have_sorted out=have_tr2 (drop=_name_);
	var PREVIOUS_NAME;
	by Contract_ID P_ID DESC YEAR NAME H P	PACE D	C	M	F	OS	SN	DE;
run;

data want2;
	set have_tr2;
	length PREVIOUS_NAME $100.;
	PREVIOUS_NAME = catx(", ", of col:);
	drop col:;
run;

data want;
	retain Contract_ID	P_ID YEAR NAME PREVIOUS_P_ID PREVIOUS_NAME DESC H P PACE D	C	M	F	OS	SN	DE;
	merge want1 want2;
	by Contract_ID P_ID DESC;
run;

Best,

Re: Rolling up Values and concatenate with unique description

bknitch — Tue, 05 May 2020 14:46:33 GMT

Hi @ed_sas_member thank you for the suggestion, unfortunately a transpose isn't plausible with the data set i'm working with, this is only a snippet of the fields that are contained within this data set. There are other changing variables per previous_plan_id that cause the transpose not to function correctly. I'm looking for more of a rollup or retain function where desc= 'Consolidated' and then concatenating the fields.With this step i would have to build several transposed tables excluding several fields and then merging.

Re: Rolling up Values and concatenate with unique description

ballardw — Tue, 05 May 2020 15:08:36 GMT

@bknitch wrote:

Hi @ed_sas_member thank you for the suggestion, unfortunately a transpose isn't plausible with the data set i'm working with, this is only a snippet of the fields that are contained within this data set. There are other changing variables per previous_plan_id that cause the transpose not to function correctly. I'm looking for more of a rollup or retain function where desc= 'Consolidated' and then concatenating the fields.With this step i would have to build several transposed tables excluding several fields and then merging.

KEEP on the variables needed for Proc transpose. The Data set option KEEP (or drop) is always available to reduce a data set:

Proc transpose data=have (keep= <list variables needed in the transpose> where=(desc='Consolidated'))

<rest of transpose code>.

Without rules as to how to select other variable values the result of the data would likely have to be merged back to your original.