Thats typical garbage Excel data unfortunately.  You will need to do some post processing to get it into a working state.  So you have this: proc import out=work.exambday4_0 datafile="\\Client\D$\SASDATA\Year_2_Exam_B_Day_4_0-morning.xlsx" bdms=excelcs replace; range="Sheet1$"; run; And note how I use the code window - its the {i} above post area - and don't code all in shouting. To get the data you show there.  First you need to clear up the missing year/exam/day/time data (done by coalesce() function below) and merge the two rows so you have one row. proc sql; create table want as select coalesce(a.year,b.year) as year, coalesce(a.exam,b.exam) as exam, coalesce(a.day,b.day) as day, coalesce(a.time,b.time) as time, a.q1 as p_q1, a.q2 as p_q2, a.q3 as p_q3, a.q4 as p_q4, a.q5 as p_q5, a.q6 as p_q6, a.overall as overall_p, b.q1 as h_q1, b.q2 as h_q2, b.q3 as h_q3, b.q4 as h_q4, b.q5 as h_q5, b.q6 as h_q6, b.overall as overall_h from (select * from have where type="Percent") a full join (select * from have where type="Hours") b on 1=1; quit; You can then do further processsing on this data, such as stacking all sheets (with which you have done the above) and summing something like: data want; set want; hours=sum(of h_:); perc=sum(of p_:); run; Take it as a good example of why not to use Excel for data storage!  ... View more