BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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;

 

3 REPLIES 3
ballardw
Super User

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
Obsidian | Level 7

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

 

proc contents data=summary12;

run;

PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1025 views
  • 0 likes
  • 4 in conversation