topic Delete spaces in unknown variables in SAS Programming

Delete spaces in unknown variables

FP12 — Thu, 24 Aug 2017 11:23:46 GMT

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,

Re: Delete spaces in unknown variables

PeterClemmensen — Thu, 24 Aug 2017 11:37:32 GMT

Set

options validvarname=v7;

as described here

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

Re: Delete spaces in unknown variables

Oligolas — Thu, 24 Aug 2017 11:41:14 GMT

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.

Re: Delete spaces in unknown variables

RW9 — Thu, 24 Aug 2017 12:04:19 GMT

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

Re: Delete spaces in unknown variables

FP12 — Thu, 24 Aug 2017 13:46:27 GMT

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

Re: Delete spaces in unknown variables

FP12 — Thu, 24 Aug 2017 13:48:28 GMT

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 😞

Re: Delete spaces in unknown variables

PeterClemmensen — Fri, 25 Aug 2017 05:51:12 GMT

Anytime, glad to help 🙂