import an excel data that some observations have multiple records

[tinghlin]

the original excel data is as follows:

Is it Possible to use proc import or other methods to import this filw?

i want the imported data as follows:

[jimbarbour · Accepted solution]

@tinghlin,

It takes a bit of coding, but you can definitely do that.  Here are the results, and below the results is the SAS code.

Jim

LIBNAME	School01	XLSX	"&Path\SampleData\Vertical_Merged_Cells.xlsx";

DATA	Class_Data;
	DROP	_:;

	SET	School01.Class_Data;

	LENGTH	_Prior_Code			$5;

	RETAIN	_Prior_ID			0;
	RETAIN	_Prior_Year			0;
	RETAIN	_Prior_SMTR			0;
	RETAIN	_Prior_Code			' ';
	RETAIN	_Prior_NStudent		0;
	RETAIN	_Prior_Proportion	0;

	IF	MISSING(ID)					THEN
		ID				=	_Prior_ID;

	IF	MISSING(Year)				THEN
		Year			=	_Prior_Year;

	IF	MISSING(SMTR)				THEN
		SMTR			=	_Prior_SMTR;

	IF	MISSING(Code)				THEN
		Code			=	_Prior_Code;

	IF	MISSING(NStudent)			THEN
		NStudent		=	_Prior_NStudent;

	IF	MISSING(Proportion)			THEN
		Proportion		=	_Prior_Proportion;

	_Prior_ID			=	ID;
	_Prior_Year			=	Year;
	_Prior_SMTR			=	SMTR;
	_Prior_Code			=	Code;
	_Prior_NStudent		=	NStudent;
	_Prior_Proportion	=	Proportion;
RUN;