CODEWITHSUNDEEP
sassas-macroproc-sql
heraldic2
heraldic2

Reputation: 3

Create PROC SQL column in macro

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|

Upvotes: 0

Views: 289

Answers (2)

Tom
Tom

Reputation: 51621

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;

Upvotes: 1

Stu Sztukowski
Stu Sztukowski

Reputation: 12944

If you transpose your data as-is, you'll get a table that looks like this:

id  _NAME_          COL1
1   ErrorCode1      W01
1   Description1    Some Text
1   ErrorCode2      R69
1   Description2    Some Text

If we look at the pattern, there are two things we want to do:

  1. Output only when we see Description
  2. Get the value of the previous ErrorCode

We can do this a number of different ways. For this case, we'll use lag.

proc transpose data=have out=have_tpose;
    by id;
    var ErrorCode1--Description2;
run;

data want;
    set have_tpose;
    where NOT missing(COL1);

    error_code = lag(COL1);

    if(find(upcase(_NAME_), 'DESCRIPTION') ) then do;
        description = COL1;
        output;
    end;

    keep id error_code description;
run;

Output:

id  error_code  description
1   W01         Some Text
1   R69         Some Text
2   R15         Some Text
3   W1000       Some Text
4   R42         Some Text

Upvotes: 0

Related Questions