Dynamic Keep=() When Column Names have Spaces

[DavidPhillips2]

proc sql;
select name into :var_list
from TestImportCols3
where year2 = '2016';
quit;

data year2016; set work.combined (keep=(&var_list));

run;

Name contains values like:

Grand total (C2016_B)

that have spaces.  How can I include this in the keep list?

[Astounding]

The spaces are already included, just based on the code that you have shown.

However, the question becomes how can you tell when one value of NAME ends and the next value begins?  To do that, you would need to insert a separator that doesn't appear in any of the NAME values.  For example:

select name into : var_list separated by '#'

Later, when you examine &VAR_LIST, you can expect that the occurrence of # separates one NAME from the next.  Inside a macro language program, you might have something along the lines of:

%do k=1 %to 5;

   %let next_name = %scan(&var_list, &k, #) ;

   %put &next_name;

%end;

[DavidPhillips2]

Astounding, does this example keep particular columns?  I’m not following this logic.

[novinosrin]

convert to sas name literal using nliteral function 

and then your keep should have no problems i think

proc sql;
select nliteral(name) into :var_list separated by ' '
from TestImportCols3
where year2 = '2016';
quit;

[DavidPhillips2]

This is helpful but I’m running into a complication where some of the text contains names like master’s.

[novinosrin]

nliteral will comfortably take care of the embedded ' token in master's as it would convert into a text and wrap automatically with double quotes. The automatic tokenisation takes care of it particularly when master's is a value of variable coming from a dataset as i see in your example. 

[DavidPhillips2]

I am researching nliteral, where do you use nliteral?

[novinosrin]

Ok basically when you are using variable names that do not conform to standard sas names, you may use the option of validvarname=any  .

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm

ANY

• SAS variable names can be up to 32 characters in length.

• The name can contain special and multi-byte characters not to exceed 32 bytes.

• The name cannot contain any null bytes.

• A name can contain blanks. The leading blanks are preserved, but the trailing blanks are ignored.

• A name must contain at least one character. A name with all blanks is not permitted.

• The name can start with or contain any characters, including blanks.

Once the option is set, the use of nliteral function basically converts with the wrapper "blah blah"N to make SAS understand it is deemed validvarname

[SuryaKiran · Accepted solution]

Try this if you don't want to change the variable names into sas naming convention.

proc sql;
select '"'||var||'"n' into:vars separated by " "
from test;
quit;

[DavidPhillips2]

This worked:

proc sql;
select '"'||name||'"n' into:var_list separated by " "
from TestImportCols3
where year2 = '2016';
quit;

data year2016; set work.combined (keep=&var_list);

run;

[DavidPhillips2]

Using similar logic I run into an issue with:

proc sql;

     select '"'||name||'"n=' || '"'||colnameCopy ||'"n' into: renameString separated by " "

     from TestImportCols3

     where year2 = '2016';

quit;

data year2016; set work.combined (rename=&renameString);

run;

It looks like I am running into an error with the rename function using the same logic when I rename

“Grand total men (C2016_B” to “M”

Not sure if this is due to the way I have ' ' n setup in the renameString above.

[ballardw]

Take a very close look at the results of

proc sql;

     select '"'||name||'"n=' || '"'||colnameCopy ||'"n' into: renameString separated by " "

     from TestImportCols3

     where year2 = '2016';

quit;

Look to see if any of the elements have spaces that you do not expect for the Name or colnamecopy variables. The || concatenation operator usually pads the result with blanks to format length. See

data junk;
   informat name $15.;
   input name;
datalines;
Fred
John
Somethinglong
;
run;

proc sql;
   select '"'||name||'"n=' into : string separated by ' '
   from junk;
run;
%put &string;

Try || strip(variablename)|| when using the || concatenation.

Or

select cats('"',name,'"n=','"',colnamecopy,'"n') into

[DavidPhillips2]

The strip function seems to have made progress.  I'm running into an error on:

"Grand total women (C2016_B)"n="Wn"n

ERROR 22-7: Invalid option name "Grand total women (C2016_B)"N.

I am currently playing with table 'junk' to see if I can recreate the error on a non-big data set.  I'm working with 3000 columns.

[DavidPhillips2]

proc sql;
create table TestImportCols3 (
name varchar(30),
colnameCopy varchar(30)
);

insert into TestImportCols3 ( name, colnameCopy) values ('Grand total women (C2016_B)', 'Wn');
insert into TestImportCols3 ( name, colnameCopy) values ('Master''s', 'M');

create table combined (
b varchar(30)
);

insert into combined (b) values ('fifkgj');
quit;

data combined; set combined (rename=(b ="Grand total women (C2016_B)"n));
run;

proc sql;
select '"'|| strip(name) ||'"n=' || '"'|| strip(colnameCopy) ||'"n' into: renameString separated by " "
from TestImportCols3;
quit;

data year2016; set work.combined (rename=&renameString);

run;

produces:

48 "Grand total women (C2016_B)"n="Wn"n "Master's"n="M"n
___________
22
ERROR 22-7: Invalid option name "Master's"N.

Due to the '

[DavidPhillips2]

Its not the ' that is causing the error its the second record.  Taking the ' out results in the same thing.

proc sql;
create table TestImportCols3 (
name varchar(30),
colnameCopy varchar(30)
);

insert into TestImportCols3 ( name, colnameCopy) values ('Grand total women (C2016_B)', 'Wn');
insert into TestImportCols3 ( name, colnameCopy) values ('Masters', 'M');

create table combined (
b varchar(30)
);

insert into combined (b) values ('fifkgj');
quit;

data combined; set combined (rename=(b ="Grand total women (C2016_B)"n));
run;

proc sql;
select '"'|| strip(name) ||'"n=' || '"'|| strip(colnameCopy) ||'"n' into: renameString separated by " "
from TestImportCols3;
quit;

data year2016; set work.combined (rename=&renameString);

run;

47 "Grand total women (C2016_B)"n="Wn"n "Masters"n="M"n
__________
22
ERROR 22-7: Invalid option name "Masters"N.

 This has the same error:

data year2016; set work.combined (rename="Grand total women (C2016_B)"n="Wn"n Masters=M);

run;