topic Re: PROC TRANSPOSE order by Fiscal Year in SAS Programming

PROC TRANSPOSE order by Fiscal Year

twildone — Fri, 12 Aug 2016 17:12:59 GMT

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

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;

Re: PROC TRANSPOSE order by Fiscal Year

ballardw — Fri, 12 Aug 2016 17:39:47 GMT

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 /

Re: PROC TRANSPOSE order by Fiscal Year

RickAster — Fri, 12 Aug 2016 19:01:56 GMT

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

proc contents data=summary12;

run;

Re: PROC TRANSPOSE order by Fiscal Year

PGStats — Sat, 13 Aug 2016 02:18:44 GMT

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;