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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.