BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
Hi,
Sometimes the variables given in excel come with spaces in front of them.SAS reads them with an underscore followed by the variable name.How to avoid this?

for instance, NDC is the field name.when we recieve the data in excel,there will be space in front of NDC so SAS reads it as _NDC.
3 REPLIES 3
Paige
Quartz | Level 8
I can think of two ways to avoid this.

1) Don't put spaces in front of variable names in Excel
2) Write a macro to remove underscores if they are the first character of a variable name.
SASPhile
Quartz | Level 8
I'm sharing the code with you all:
data abc(keep=name column2 column3);
set sashelp.vcolumn;
where libname='WORK' and memname='FAIRVIEW_FIN';
if substr(name,1,1) = '_' then column2 = substr(name, 2, length(name));
else column2=name;
column3=left(trim(name))||'='||column2;
run;


proc sql;
select distinct column3 into: new_column seperated by ' '
from abc;
quit;
run;




data Fairview_fin (rename = (&new_column));
set Fairview_fin;
run;
Patrick
Opal | Level 21
You can use the SAS option "validvarname=any".
If "any" is set then the Excel variable names won't be converted to comply with SAS naming conventions during import.

You can do it - but I wouldn't as it will cause you quite a bit of pain. Variables with non-conforming names must be addresses as literals meaning that whenever you use the variable you have to quote the name in the form: ''n

For SAS EG 4.2
In step 2 of the import wizard there is a checkbox "rename columns to comply with SAS naming conventions". Make sure that this checkbox is not ticked if you want to avoid the conversion.
Again: I recommend to choose conversion. You still keep the excel source variable names as SAS variable labels.

HTH
Patrick

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
  • 3 replies
  • 9588 views
  • 0 likes
  • 3 in conversation