sorting columns using macro variable problem

[sunless07652]

Hi, I'm trying to reorder the columns of a dataset using proc sql and a macro variable containing the sorted list of variables I want.

my code:

proc transpose data = WORK.MEMBERS OUT=WORK.SUMMARY prefix=svcmth_;
BY MEMBER_MAID MEMBER_ID RegionName_N;
VAR SUM_of_Month_Paid;
ID svc_mth;
RUN;

my table SUMMARY after the proc transpose has fields:

Name Type Length Format Informat Label
MEMBER_MAID Character 13 $CHAR13. $CHAR13.
MEMBER_ID Character 8 $CHAR8. $CHAR8.
RegionName_N Character 13 $CHAR13. $CHAR13.
_NAME_ Character 17 NAME OF FORMER VARIABLE
svcmth_2018-01 Numeric 8 BEST12.
svcmth_2017-07 Numeric 8 BEST12.
svcmth_2016-10 Numeric 8 BEST12.
svcmth_2016-07 Numeric 8 BEST12.
svcmth_2017-01 Numeric 8 BEST12.
svcmth_2017-04 Numeric 8 BEST12.
svcmth_2018-04 Numeric 8 BEST12.
svcmth_2017-08 Numeric 8 BEST12.
svcmth_2016-12 Numeric 8 BEST12.
svcmth_2016-11 Numeric 8 BEST12.
svcmth_2017-02 Numeric 8 BEST12.
svcmth_2017-03 Numeric 8 BEST12.
svcmth_2017-06 Numeric 8 BEST12.
svcmth_2017-10 Numeric 8 BEST12.
svcmth_2018-03 Numeric 8 BEST12.
svcmth_2018-06 Numeric 8 BEST12.
svcmth_2017-11 Numeric 8 BEST12.
svcmth_2018-02 Numeric 8 BEST12.
svcmth_2017-09 Numeric 8 BEST12.
svcmth_2017-12 Numeric 8 BEST12.
svcmth_2016-08 Numeric 8 BEST12.
svcmth_2018-05 Numeric 8 BEST12.
svcmth_2017-05 Numeric 8 BEST12.
svcmth_2016-09 Numeric 8 BEST12.

proc contents data = WORK.SUMMARY out=col_names (keep=name);
run;

proc sort data = col_names;
by name;
run;

data col_names;
set col_names;

if name in ('MEMBER_ID', 'MEMBER_MAID', 'RegionName_N', '_NAME_') then delete;
RUN;

data test;
set col_names;
BY NAME;
RETAIN sorted_cols;
length sorted_cols $2500.;
if _n_ =1 then sorted_cols = name;
else sorted_cols = catx(', ', sorted_cols, name);
call symput('sorted_cols', sorted_cols);
run;

%put &sorted_cols;

proc sql;
create table SUMMARY2 as
select MEMBER_ID, MEMBER_MAID, RegionName_N, &sorted_cols
from SUMMARY;
quit;

SAS gives me an error on the last proc sql step:

79 %put &sorted_cols;
svcmth_2016-07, svcmth_2016-08, svcmth_2016-09, svcmth_2016-10, svcmth_2016-11, svcmth_2016-12, svcmth_2017-01, svcmth_2017-02,
svcmth_2017-03, svcmth_2017-04, svcmth_2017-05, svcmth_2017-06, svcmth_2017-07, svcmth_2017-08, svcmth_2017-09, svcmth_2017-10,
svcmth_2017-11, svcmth_2017-12, svcmth_2018-01, svcmth_2018-02, svcmth_2018-03, svcmth_2018-04, svcmth_2018-05, svcmth_2018-06
80
81 proc sql;
82 create table op_summary2 as
83 select MEMBER_ID, MEMBER_MAID, RegionName_N, &sorted_cols
84 from OP_SUMMARY;
ERROR: The following columns were not found in the contributing tables: svcmth_2016, svcmth_2017, svcmth_2018.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
85 quit;

I don't understand why it's giving me this error, especially since the %put statement shows that the macro variable "sorted_cols" does not contain these columns (svcmth_2016, svcmth_2017, svcmth_2018).

[Astounding]

You are trying to use non-standard variable names, since a dash is not normally a valid character when naming a variable.  You will have to change the way you refer to those variables.

Doesn't work:  svcmth_2018-01

Works:  "svcmth_2018-01"n

Another option needs to be in place to allow these non-standard names.  I think it looks like this:  

options validvarname=any;

It shouldn't be too difficult to get your SQL code to produce a macro variable with the proper form for the variable names.  Instead of selecting NAME, you would need to select an expression that includes the name in double quotes, with the letter n following.

Alternatively, you could take the simple route and change the values of SVC_MTH.  Either removing the dashes, or replacing them with underscores would work.

[Tom · Accepted solution]

The nliteral() function will convert non-standard names to name literals.

[PGStats]

During a transpose, SAS creates the new columns in the order in which it meets the ID values. So if all columns are represented in your first MEMBER_MAID/MEMBER_ID/RegionName_N by-group, you can get sorted columns directly by sorting ID values prior to transposing:

proc sort data=MEMBERS; by MEMBER_MAID MEMBER_ID RegionName_N svc_mth; run;

proc transpose data=MEMBERS OUT=SUMMARY prefix=svcmth_;
BY MEMBER_MAID MEMBER_ID RegionName_N;
VAR SUM_of_Month_Paid;
ID svc_mth;
RUN; 

(untested)

[sunless07652]

Thanks everyone.  It was due to the non-standard naming of my variable names, and nliteral() solved the issue.