DATA Step, Macro, Functions and more

Delete spaces in unknown variables

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Delete spaces in unknown variables

[ Edited ]

Hi,

 

Following my topic of yesterday:

https://communities.sas.com/t5/Base-SAS-Programming/proc-import-excel-sheet/m-p/390276#M93589

 

I don't have another choice than importing an excel sheet with column names as equals to the first row of my sheet.

The two major problems are:

- the first row can change from one sheet to another then the column names also

- some column names have spaces :-/

 

I can manage the first problem by looking for column names with sashelp.vcolumn

The second is more complex. I think I can, but it's an hard work because I need to use names in several ways, transposing, aggregating,...

 

Is there a way to replace all spaces by this: _ for all variables of a table (whatever they are) without naming them?

 

Thanks,

 


Accepted Solutions
Solution
‎08-24-2017 09:44 AM
PROC Star
Posts: 768

Re: Delete spaces in unknown variables


All Replies
Solution
‎08-24-2017 09:44 AM
PROC Star
Posts: 768

Re: Delete spaces in unknown variables

Contributor
Posts: 34

Re: Delete spaces in unknown variables

[ Edited ]

Wooh you just saved my life, nothing less! Thanks.

I was trying to find a difficult solution and in one line you solve my problem...

I feel so stupid right now ^^

PROC Star
Posts: 768

Re: Delete spaces in unknown variables

Anytime, glad to help Smiley Happy

Frequent Contributor
Posts: 129

Re: Delete spaces in unknown variables

Hi,

 

when using proc import with getnames=yes SAS get rid of the spaces in the name and inserts underscore, so I don't get what's the problem here.

 

if sometimes your data contain the names in first row and sometimes not, you should try identify the sheets having names and those without and import separately.

 

If you want to automate the whole thing I think you have no other solution than importing the whole document twice, one time with yes and one time with no.

 

Without test data and approximation of the number of sheets/frequency it's difficult to say what's best.

________________________

- Cheers -

Super User
Super User
Posts: 7,997

Re: Delete spaces in unknown variables

" but it's an hard work because" - the answer to this is because you are forced to use a very poor data source.  No matter what you do, or what you program, it will never be a stable efficient process if the input is not.  Whenever I get this scenario, after pushing back, then I resort to other methods, for instance:

"Well, due to the nature of the source, the programming will take X hours longer (costing xyz), and the ongoing cost per unit (i.e. import) would be xyz for maintenance"

Its amazong how things get changed when you lay it out like that.

Contributor
Posts: 34

Re: Delete spaces in unknown variables

I don't deny it...

But the house values are to ask for better quality but find an alternative while waiting for it.

So as you say, I'm forced to use  poor quality Smiley Sad

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 151 views
  • 4 likes
  • 4 in conversation