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).
... View more