Delete spaces in unknown variables

[FP12]

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,

[PeterClemmensen · Accepted solution]

Set

options validvarname=v7;

as described here

http://support.sas.com/documentation/cdl/en/engfedsrv/70118/HTML/default/viewer.htm#p1ffkpdm6kbjawn1...

[FP12]

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 ^^

[PeterClemmensen]

Anytime, glad to help 🙂

[Oligolas]

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.

[RW9]

" 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.

[FP12]

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 😞