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:
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: