SAS/SQL Ordering Variables Dynamically

Question

I'm trying to organize a dataset in a specific way with a list of variables that changes. The issue I'm having is that I don't always know the actual number of variables I'm going to have in my dataset. I've done this previously with either a PROC SQL statement or a RETAIN statement after the data statement where the list of variables was static.

My data looks like this:

APPNUM   DATE   REASON1  REASON2 REASON3 REASON4 NAME1 NAME2 NAME3 NAME4
123    1/1/2017   X        Y        Z      A     Jon   Mary  Tom   Suzie

I want it to look like this:

APPNUM   DATE   REASON1  NAME1 REASON2 NAME2 etc
123    1/1/2017   X       Jon   Y      Mary  etc

This would be easy with sql or a retain statement. However, I am using loops, etc to pull these variables together, and the number of variables presented is dependent upon my input data. Some days there may be 20 instances of REASON/NAME and others there may be 1 of each.

I tried the below code to pull a list of variable names, then order the APPNUM, DATE, then finally order by the LAST digit of the variable name. I.E. 1,1,2,2,3,3 - but I was unsuccessful. The list was being stored properly - no errors, but when resolving the value of &VARLIST. they are not ordered as expected. Has anyone ever tried and accomplished this?

PROC SQL;
SELECT NAME INTO :VARLIST SEPARATED BY ','
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = 'WORK'
AND MEMNAME = 'SFINAL'
ORDER BY NAME, SUBSTR(NAME,LENGTH(NAME)-1);
QUIT;

The above code would order something like this:

APPNUM, DATE, NAME1...2...3..., REASON1...2...3...

and not:

APPNUM, DATE, NAME1, REASON1, NAME2, REASON2....

DomPazz DomPazz · Accepted Answer · 2017-08-09T20:01:52

Two problems.

First, your order on the ORDER BY is backwards.

Second, your SUBSTR() call is not correct. You have an arbitrary length number at the end. You don't know how many characters that will be. You best bet is to read that number string, convert to a number, and then order by that.

data test;
array name[20];
array reason[20];
format appnum best. date date9.;
run;

proc sql noprint;
SELECT  NAME INTO  :VARLIST SEPARATED BY ','
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = 'WORK'
AND MEMNAME = 'TEST'
and (upcase(NAME) like 'NAME%' or upcase(NAME) like 'REASON%') 
ORDER BY input(compress(compress(name,'name'),'reason'),best.), NAME ;
quit;

%put &varlist;

proc sql noprint;
create table test2 as
select APPNUM, DATE, &varlist
from test;
quit;

SAS/SQL Ordering Variables Dynamically

1 Answers