Column Sorting with PROC Transpose in SAS

Question

I am using below mentioned code to get the columns sorted dynamically after proc transpose. I have gone a lot of solutions for this solution. But now I am getting an error if I run

data work.AB ;
input name $ class $ dt $ gpa $;
datalines;
JOHN 1 201607 C-
JOHN 1 201608 C+
JOHN 1 201702 B-
JOHN 2 201608 A
NICK 1 201608 A
NICK 1 201707 A
MIKE 2 201608 B
MIKE 2 201607 B
MIKE 2 201707 B+
MIKE 2 201702 B
BOB 3 201702 D
BOB 3 201607 C
BOB 3 201707 C
;
proc sort data=work.AB;
by NAME ClASS dt;
run;

PROC TRANSPOSE DATA = AB OUT = ABC(drop=_name_) ;
BY nAME cLASS; 
VAR GPA;
ID dt; 
RUN ;

proc sql ;
create table test as 
select name into : list separated by ' '
from dictionary.columns
where libname='WORK' and memname='ABC'
order by input(substr(name,anydigit(name)),best32.)
;
quit;

%put &list;

data want;
retain &list;
set ABC;
run;

Error that I get is

22 GOPTIONS ACCESSIBLE;
WARNING: Apparent symbolic reference LIST not resolved.
23 %put &list;
&list

24 data want;
25 retain &list;
_
22
200
WARNING: Apparent symbolic reference LIST not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.
ERROR 200-322: The symbol is not recognized and will be ignored.

26 set ABC;
27 run;

Kindly suggest.

You need to give more information (presumably, the log from the first part of the program). The SQL SELECT INTO bit isn't working, for whatever reason, but we don't know why. — Joe
I just mentioned that Error log, which I was getting. Rest everything is working fine for me. I am not able to find solution to the mentioned error. Perhaps you can use proc sql noprint; select name into : list separated by ' ' from dictionary.columns where libname=WORK' and memname='ABC' order by input(substr(name,anydigit(name)),best32.) quit; — Aditya Kaushik

Tom Tom · Accepted Answer · 2017-07-08T00:50:05

You cannot put the values from the same SELECT statement into both a dataset and macro variables. Remove the create table test as from your SQL code.

You also might want to suppress some of the warnings by changing the query to:

proc sql noprint ;
select case when (not anydigit(name)) then -1
            else input(substr(name,anydigit(name)),?32.)
       end as order
     , name
  into :list
     , :list separated by ' '
from dictionary.columns
where libname='WORK' and memname='ABC'
order by 1
;
quit;

%put &list;

Column Sorting with PROC Transpose in SAS

1 Answers