Create PROC SQL column in macro

Question

I have a syntax question that I can't find the answer to.

I have a data set that I import in which cannot be changed that goes up to 25 sets of column data. I looked into PROC TRANSPOSE, but for multiple columns I couldn't find a way to make it work without a macro.

|ID|Error Code 1|Description 1|Error Code 2|Description 2|....|....|Error Code|Description 25|
|1|W01|Some Text|R69|Some Text|....|....|R42|Some Text|
|2|R15|Some Text|||||||
|3|W1000|Some Text|R42|Some Text|||||
|4|R42|Some Text|||||||
|5|W500|Some Text|R69|Some Text|||||

What I need to do is get each error code and description into another table so that it can be compared and new data put in.

I have written a macro to do the job

%macro MacroTranspose;
 PROC SQL;
       %DO i=1 %TO 25;
            %IF &i=25 %THEN %DO;
                 INSERT INTO work.SingleRows(LoanNumber,ErrorCode,ErrorDesc)
                 SELECT 'Loan Number'n, 'Error Code 'n, 'Description 25'n FROM RawImport;
            %END;
            %ELSE %DO;
                 INSERT INTO work.SingleRows(LoanNumber,ErrorCode,ErrorDesc)
                 SELECT 'Loan Number'n, CAT('Error Code ', &i), CAT('Description ',&i)  FROM RawImport;                
            %END;
       %END;
 QUIT;

%mend; %MacroTranspose;

However I cannot figure out to get the dynamic column names in this line:

SELECT 'Loan Number'n, CAT('Error Code ', &i), CAT('Description ',&i)  FROM RawImport; 

to be formatted in such a way as SAS views it as an actual column. I keep getting it returned as either a literal or error.

Desired output:

|LoanNumber|ErrorCode|ErrorDesc|
|1|W01|Some Text|
|1|R69|Some Text|
|1|....|....|
|1|R42|Some Text|
|2|R15|Some Text|
|3|W1000|Some Text|
|3|R42|Some Text|

Answer 1

As to the asked Y part of your XY problem the direct answer it use double quote characters for the name literals so you can reference your loop macro variable.

SELECT 'Loan Number'n
     , "Error Code &i"n
     , "Description &i"n

Now each generated SELECT will pull from a different pair of variables.

The macro processor will ignore text in quoted strings bounded by single quote characters.

As to the actual X part of your XY problem note that macro code is not needed here at all. Just use a data step to transform the data. Make two arrays and index through the arrays outputting one observation per pair of variables.

data SingleRows;
  set RawImport;
  array e "Error Code 1"n-"Error Code 25"n ;
  array d "Description 1"n="Description 25"n;
  do index=1 to dim(e);
    ErrorCode = e[index];
    ErrorDesc = d[index];
    output;
  end;
  keep 'Loan Number'n index ErrorCode ErrorDesc ;
  rename 'Loan Number'n=LoanNumber ;
run;