topic Re: Column Sorting with Proc Transpose in SAS Enterprise Guide

Column Sorting with Proc Transpose

Aditya81769 — Fri, 07 Jul 2017 20:12:23 GMT

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.

Re: Column Sorting with Proc Transpose

art297 — Sun, 09 Jul 2017 20:35:20 GMT

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

Re: Column Sorting with Proc Transpose

Aditya81769 — Sun, 09 Jul 2017 20:22:07 GMT

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

Re: Column Sorting with Proc Transpose

art297 — Sun, 09 Jul 2017 20:33:35 GMT

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

Re: Column Sorting with Proc Transpose

Aditya81769 — Mon, 10 Jul 2017 04:56:20 GMT

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

Re: Column Sorting with Proc Transpose

art297 — Mon, 10 Jul 2017 05:21:53 GMT

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