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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.