PROC TRANSPOSE order by Fiscal Year

[twildone]

Hi...I am trying to transpose a dataset and when I transpose the dataset, the columns which are the corresponding fiscal years are not in the proper order. I am trying to put the dataset in order by a macro variable and I am getting the error message below. I am thinking that it is because of the forward slash "/" that is included in the fiscal year. Any suggestions how I can get around this. Thanks.

proc sql ;

select distinct catx(' ',FiscalYear)

into :List1 separated by ','

from work.cexdata

order by FiscalYear;

quit;

data TOP_10_HC;

retain PIN PROD1 PROD2 &List1;

set summary12;

run;

LOG OUTPUT:

130 data TOP_10_HC;

131 retain PIN PROD1 PROD2 &List1;

NOTE: Line generated by the macro variable "LIST1".

131 2012/13,2013/14,2014/15,2015/16,2016/17

_

22

76

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_,

_CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

132 set summary12;

133 run;

[ballardw]

Perhaps sorting the data before the transpose would correct the issue beforehand?

Your macro variable does not contain valid SAS variable names.

NOTE: Line generated by the macro variable "LIST1".

131 2012/13,2013/14,2014/15,2015/16,2016/17

Note that 2012/13 is NOT valid as a variable name because 1)it starts with a digit and 2) contains a /

[RickAster]

You can use the CONTENTS procedure if you need to find out what the variable names are.

proc contents data=summary12;

run;

[PGStats]

I guess you mean to do something like this:

proc sql ;
select distinct translate(FiscalYear, "_", "/")
into :List1 separated by ' '
from cexdata
order by FiscalYear;
quit;

proc transpose data=cexdata out=summary12;
by PIN PROD1 PROD2;
var whatever;
id FiscalYear;
run;
 
data TOP_10_HC;
retain PIN PROD1 PROD2 &List1;
set summary12;
run;