topic Re: sorting columns using macro variable problem in SAS Programming

sorting columns using macro variable problem

sunless07652 — Wed, 08 Aug 2018 23:36:57 GMT

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

Re: sorting columns using macro variable problem

Astounding — Thu, 09 Aug 2018 00:42:05 GMT

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.

Re: sorting columns using macro variable problem

Tom — Thu, 09 Aug 2018 01:21:14 GMT

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

Re: sorting columns using macro variable problem

PGStats — Thu, 09 Aug 2018 03:02:39 GMT

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)

Re: sorting columns using macro variable problem

sunless07652 — Thu, 09 Aug 2018 17:34:54 GMT

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