You could put this DATA step between the import and the transpose - in which you insert 2 sentinel variables (_PRE and _POST) in location 5 and location "last".
data need;
set IMPORT.Mandatory_Trainings (keep=ID Employee_Name Overall__Training_Status General_Exclusion__Status);
retain _PRE .;
set IMPORT.Mandatory_Trainings;
retain _POST .;
run;
The "trick" here is to understand how SAS compiler builds the program data vector (PDV) in a data step.
The first SET keeps only a limited set of 4 variables, which become the 4 left-most in the PDV.
Then the retain _PRE statement appends it to the right, i.e. column 5.
Then a second SET reads all the training vars. Four of them are already on the left of _PRE, but the rest are appended to the right of _PRE.
Then the retain _POST generates the right-most var.
Then you can modify the proc transpose to always have "var _PRE--_POST" - no changing varname awareness required. And just insert a "(where=(not(name in ("_PRE","_POST"))))" dataset-name-parameter in the "out=" clause:
proc transpose Data=need
OUT=Base.Mandatory_Trainings_Pivot (where=(not(_name_ in ('_PRE','_POST'))));
BY ID Employee_Name Overall__Training_Status General_Exclusion__Status;
var _pre -- _post;
run;
... View more
