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;
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 /
You can use the CONTENTS procedure if you need to find out what the variable names are.
proc contents data=summary12;
run;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.