Column Sorting with Proc Transpose

[Aditya81769]

Hi Everyone,

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.

[art297]

You can't use proc sql to both create a table and create a macro variable at the same time.

The following (your code with the create table removed) will produce a note that the sort will produce some missing values, but I think does what you want it to do:

data 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 noprint;
/* 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;

Art, CEO, AnalystFinder.com

Note: added the initial data and input statements that were originally omitted from my post

[Aditya81769]

Hi,

I have tried the way you that you have suggested
But I am still getting the same error

Kindly suggest
Thank you for the help

[art297]

The initial data and input statements were somehow omitted from the code I proposed (I'll add them to the original post).

The following worked for me:

data 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 noprint;
/* 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;

Art, CEO, AnalystFinder.com

[Aditya81769]

Hi

Do you have any solution for the error which I am getting. If I try to run the same code which you have suggested I get the same error.

Below is the log windo that I am getting.

1 The SAS System 09:15 Friday, July 7, 2017

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 data work.AB ;
24 input name $ class $ dt $ gpa $;
25 /* calendardate=mdy(scan(dt,1),scan(dt,2),scan(dt,3)); */
26 /* format calendardate mmddyy10.;*/
27 datalines;

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.AB has 13 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds

42 ;

43
44 proc sort data=work.AB;
45 by NAME ClASS dt;
46 run;

NOTE: There were 13 observations read from the data set WORK.AB.
NOTE: The data set WORK.AB has 13 observations and 4 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

47
48 PROC TRANSPOSE DATA = AB OUT = ABC(drop=_name_) ;
49 BY nAME cLASS;
50 VAR GPA;
51 ID dt;
52 RUN ;

NOTE: There were 13 observations read from the data set WORK.AB.
NOTE: The data set WORK.ABC has 5 observations and 6 variables.
2 The SAS System 09:15 Friday, July 7, 2017

NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

53
54 proc sql noprint;
55 /*create table test as */
56 select name into : list separated by ' '
57 from dictionary.columns
58 where libname='WORK' and memname='ABC'
59 order by input(substr(name,anydigit(name)),best32.)
60 ;
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Invalid argument 2 to function SUBSTR. Missing values may be generated.
61 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds

62 %put &list;
class name 201607 201608 201702 201707
63 data want;
64 retain &list;
NOTE: Line generated by the macro variable "LIST".
64 class name 201607 201608 201702 201707
______
22
200
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.

65 set ABC;
ERROR: Variable name has been defined as both character and numeric.
ERROR: Variable class has been defined as both character and numeric.
66 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

67
68
69 GOPTIONS NOACCESSIBLE;
70 %LET _CLIENTTASKLABEL=;
71 %LET _CLIENTPROJECTPATH=;
72 %LET _CLIENTPROJECTNAME=;
73 %LET _SASPROGRAMFILE=;
74
75 ;*';*";*/;quit;run;
76 ODS _ALL_ CLOSE;
77
3 The SAS System 09:15 Friday, July 7, 2017

78
79 QUIT; RUN;
80

[art297]

Your log doesn't show it, but my best guess is that you have the SAS option VALIDVARNAME set to the value ANY.

run the following line before running the code:

options validvarname=v7;

Art, CEO, AnalystFinder.com