Unknown variable name with space query

[fabdu92]

Hi,

I have a table A, for which I don't know in advance variables names (it can change according to the file I import).

I just know I need to access the value of a row where the first column equals something.

Something like this:

SELECT jk INTO :JK1 FROM A WHERE first_column LIKE 'ZZ%'

In order to do this I access the name of the first variable and stor it in a macrovariable. The I use it:

PROC SQL NOPRINT;
SELECT name INTO : COLUMN1 from SASHELP.VCOLUMN where memname = "A" AND varnum = 1;

SELECT jk INTO :JK1 FROM A
WHERE &COLUMN1. LIKE 'ZZ%';

The problem is that usually the variable contains some spaces, like for example "RESULTS CHIR".

So I tried to modify the second query in:

SELECT jk INTO :JK1 FROM A
WHERE '&COLUMN1.'n LIKE 'ZZ%';

But when I run it the program tells me the column doesn't exist in the contributing table...

Can you help me to resolve this?

[Astounding]

Macro variable references within single quotes do not resolve.  If you are permitted to use double quotes, switch to:

"&COLUMN1."n

But if single quotes are required, a more complex expression would do the trick:

%unquote(%str(%'&COLUMN1.%'n))

[Kurt_Bremser]

---

@fabdu92 wrote:

Hi,

I have a table A, for which I don't know in advance variables names (it can change according to the file I import).

---

And this is the root of your problem. Deliberately changing variable names are bullshit, and must not be accepted, period.

Have a clear agreement over the structures of files used to deliver data to your warehouse, and write import code according to those agreements.

Do NOT use proc import in a production environment. It can be used for prototyping, but that's it.

[RW9]

Totall agree with @Kurt_Bremser here.  Any import should be documented and repeatable.  From the code you posted, it looks like your taking one piece of information and then putting that into a macro varaible, for use in further code.  So, if your basis for not just this but for other code is either unknown, or invalid, all your code will fall down - imagine a square block at the bottom of a tower, if you change that out for a triangle block, it may balance, or it may all collapse regardless of how well the rest of the tower is built.

[ballardw]

If you are having to do something to the "first column" repeatedly where you don't know the names of your variables tells me the whole process is somewhat suspect.

If you are using proc import then it is time to learn how to write a data step to read the data so you control the names, the variable types, lengths and formats.

[Reeza]

Look at the NLITERAL function to return a valid name. You can also change your options (validvarname=V7) so that variable names cannot have spaces in the first place and not have this issue at all.

[PGStats]

If your data comes from a delimited file, use option GETNAMES=NO in your import procedure. That way the first column will always be called VAR1.