BookmarkSubscribeRSS Feed
gsreddy
Fluorite | Level 6

Am reading data from excel sheet, in that i dont know how many colums are there.

I want to place * after 3 characters of every column name.

sample column names of excel sheet:

1) customer_id

2) member_id

3) customer_name

4) address

--

--

--

Desired output column names:

1) cus*tomer_id

2) mem*ber_id

3) cus*tomer_name

4) add*ress

--

--

--

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Having an asterix (*) in a dataset is not valid.  So you will not be able to do what you want there.

Also, if your data is in Excel, then I would strongly suggest you save to CSV, then write the import program yourself using datastep/infile/input statements.  You can then set labels, names formats etc. and have control over your import rather than letting proc import guess for you.  You still wont be able to use asterixs in the variable name however.

gsreddy
Fluorite | Level 6

Hi RW9

Is it possible to place '_hello_' instead of * ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, way I would do it is:

data _null_;

  set sashelp.vcolumn (where=(libname="SASHELP" and memname="CLASS")) end=last;

  if _n_=1 then call execute('data want; set sashelp.class (rename=(');

  call execute(' '||strip(name)||'='||substr(name,1,3)||'_hello_'||strip(substr(name,4)));

  if last then call execute(')); run;');

run;

You could also do it in SQL, i.e. select into macro variable, then use that macro variable as your rename.

Patrick
Opal | Level 21

That's simply a very bad idea as such names are not valid SAS names SAS(R) 9.4 Language Reference: Concepts, Fourth Edition. It could be done but then you would have to reference all your SAS variables in all your code as SAS name literals in the form: 'cus*tomer ID'n.

For such requirements use variable labels instead. SAS Procedures creating reports allow you to use the variable labels instead of the variable names for "printed" output.

The other question you're having is: How can I change variable names and variable attributes in a SAS data set using code (=automated)? This is a question which came up already many times in this forum and I suggest you do a search.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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