DATA Step, Macro, Functions and more

PROC TRANSPOSE order by Fiscal Year

Reply
Regular Contributor
Posts: 229

PROC TRANSPOSE order by Fiscal Year

[ Edited ]

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;

 

Super User
Posts: 11,343

Re: PROC TRANSPOSE order by Fiscal Year

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 /

Contributor
Posts: 22

Re: PROC TRANSPOSE order by Fiscal Year

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

 

proc contents data=summary12;

run;

Respected Advisor
Posts: 4,920

Re: PROC TRANSPOSE order by Fiscal Year

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;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 300 views
  • 0 likes
  • 4 in conversation