BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

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?

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

16 REPLIES 16
Astounding
PROC Star

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
Rhodochrosite | Level 12

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

novinosrin
Tourmaline | Level 20

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
Rhodochrosite | Level 12

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

novinosrin
Tourmaline | Level 20

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
Rhodochrosite | Level 12

I am researching nliteral, where do you use nliteral?

novinosrin
Tourmaline | Level 20

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
Meteorite | Level 14

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
DavidPhillips2
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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
Super User

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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