DATA Step, Macro, Functions and more

Dynamic Keep=() When Column Names have Spaces

Accepted Solution Solved
Reply
Highlighted
Valued Guide
Posts: 531
Accepted Solution

Dynamic Keep=() When Column Names have Spaces

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?


Accepted Solutions
Solution
2 weeks ago
Regular Contributor
Posts: 165

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

Posted in reply to DavidPhillips2

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;

Thanks,
Suryakiran

View solution in original post


All Replies
Super User
Posts: 5,987

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

[ Edited ]
Posted in reply to DavidPhillips2

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;

Valued Guide
Posts: 531

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

Posted in reply to Astounding

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

PROC Star
Posts: 827

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

[ Edited ]
Posted in reply to DavidPhillips2

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;
Valued Guide
Posts: 531

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

Posted in reply to novinosrin

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

PROC Star
Posts: 827

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

Posted in reply to DavidPhillips2

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. 

Valued Guide
Posts: 531

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

[ Edited ]
Posted in reply to novinosrin

I am researching nliteral, where do you use nliteral?

PROC Star
Posts: 827

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

Posted in reply to DavidPhillips2

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

 

Solution
2 weeks ago
Regular Contributor
Posts: 165

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

Posted in reply to DavidPhillips2

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;

Thanks,
Suryakiran
Valued Guide
Posts: 531

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

Posted in reply to SuryaKiran

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;

Valued Guide
Posts: 531

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

Posted in reply to 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.

Super User
Posts: 12,148

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

Posted in reply to DavidPhillips2

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

Valued Guide
Posts: 531

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

[ Edited ]

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.

Valued Guide
Posts: 531

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

[ Edited ]
Posted in reply to 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 '

Valued Guide
Posts: 531

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

[ Edited ]
Posted in reply to 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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 197 views
  • 1 like
  • 5 in conversation