topic Re: Dynamic Keep=() When Column Names have Spaces in SAS Programming

Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 20:14:47 GMT

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?

Re: Dynamic Keep=() When Column Names have Spaces

Astounding — Thu, 08 Feb 2018 20:24:15 GMT

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;

Re: Dynamic Keep=() When Column Names have Spaces

novinosrin — Thu, 08 Feb 2018 20:28:20 GMT

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;

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 20:57:57 GMT

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

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 21:01:29 GMT

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

Re: Dynamic Keep=() When Column Names have Spaces

novinosrin — Thu, 08 Feb 2018 21:04:21 GMT

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.

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 21:08:33 GMT

I am researching nliteral, where do you use nliteral?

Re: Dynamic Keep=() When Column Names have Spaces

novinosrin — Thu, 08 Feb 2018 21:13:18 GMT

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

Re: Dynamic Keep=() When Column Names have Spaces

SuryaKiran — Thu, 08 Feb 2018 21:16:30 GMT

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;

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 21:22:13 GMT

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;

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Thu, 08 Feb 2018 21:59:15 GMT

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.

Re: Dynamic Keep=() When Column Names have Spaces

ballardw — Thu, 08 Feb 2018 23:30:44 GMT

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.

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

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Fri, 09 Feb 2018 14:17:52 GMT

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.

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Fri, 09 Feb 2018 15:06:57 GMT

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 '

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Fri, 09 Feb 2018 16:20:58 GMT

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;

Re: Dynamic Keep=() When Column Names have Spaces

DavidPhillips2 — Fri, 09 Feb 2018 16:35:44 GMT

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

run;

I was missing a set of ()

Re: Dynamic Keep=() When Column Names have Spaces

ballardw — Fri, 09 Feb 2018 16:54:16 GMT

@DavidPhillips2 wrote:

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.

Testing renaming can actually be done with a very small data set. You don't even need any observations unless you also want to do something like changing formats:

Data testset;

set bigset (obs=1);

run;

Or obs =0 if you don't need to see actual values.