First off your original data is the one that is transposed, your want data is normalised.
Secondly, where have you got the "using MACRO" part from? There is no need to use macro, ever. It has its place, but fundamentally it is a find replace text generator. What you want can be done simply in datasteps or procedures in Base SAS - which is the programming language.
Thirdly, you say you want to transpose data in the same Excel file, you would not do this in SAS, in SAS you read the data, process the data, then write the data out to a file. You can read the data in a number of ways depending on what SAS version you are using, what Excel version you are using etc. of which you have not mentioned anything. I am not going over every possible connotation of the two.
To be honest, looking at that dreadful format of data, I would probably - if it was me - invest some time in Office VBA within Excel itself and process the data, drop the SAS part totally. Should be pretty straight-forward, just a matter of selecting range.start to end, then looping over to the right each observation and copying to a new worksheet.
Another option is to read the whole sheet into one dataset in SAS, then in SAS write a datastep which goes over each of the columns and outputs the results, maybe something like:
data want (keep=id name time count);
set have;
array col{7};
array heads{7};
if col{1}="" then delete;
/* Headers */
if char(col{i},1) in ("W","Y","M") then do;
do j=1 to 7;
heads{j}=col{j};
end;
end;
/* Data */
else do;
do j=1 to 7;
time=heads{j};
count=col{j};
output;
end;
run;
