topic Split merged row data into two rows through SAS code in SAS Programming

Split merged row data into two rows through SAS code

india2016 — Wed, 24 Jul 2024 11:34:09 GMT

Hello Experts,

I have below table in SAS with two values merged in row for column C like below and this table is imported from Excel file so Alt+Enter used to enter two values in same row.

A	B	C	D
101	Aks	likwise asksa	422
102	Pat	Bck	23
103	Bha	asas	122
104	Dok	asoio asjka	124

and wanted to output like split those values into seperate rows like below using SAS code

A	B	C	D
101	Aks	likwise	422
101	Aks	asksa	422
102	Pat	Bck	23
103	Bha	asas	122
104	Dok	asoio	124
104	Dok	asjka	124

Is it possible to do ? I tried a lot ways to solve this issue but could not able to solve this. Please can You please suggest a way to resolve this issue. it would be much appriciated. Thanks in advance!!

Re: Split merged row data into two rows through SAS code

SASJedi — Wed, 24 Jul 2024 12:21:44 GMT

Try this:

/* Assign a libref to the Excel file */
libname xl xlsx "have.xlsx";
data want;
	/* Read the data from the Excel tab */
	/* Renaming the C column so we can create a new C */
	set xl.have (rename=(c=_C));
	/* Set up the new C variable */
	length C $10;
	/* 0D0A hex is the CTRL-Enter key value */
	_ctrlEnter='0D0A'x;
	/* If the text contains CTRL-Enter, split it up */
	if find(_C,'0D0A'x) then do;
		/* Count the number of CTRL-Enter character in the text */
		/* and output a new row for each value */
		do _i=1 to countc(_C,_ctrlEnter);
			C=scan(_C,_i,_ctrlEnter);
			output;
		end;
	end;
	/* Otherwise, just write out a record */
	else do;
		C=_c;
		output;
	end;
	/* Get rid of all the temporary variables (names begin with _)*/
	drop _:;
run;
/* Clear the Exel libref */
libname xl clear;

Re: Split merged row data into two rows through SAS code

india2016 — Wed, 24 Jul 2024 14:28:35 GMT

Thank you SASJedi. I have tried this and works fine for me. . Now I can modify as per my requirements.

Thank you so much