topic Unknown variable name with space query in SAS Programming

Unknown variable name with space query

fabdu92 — Wed, 23 Aug 2017 13:40:17 GMT

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?

Re: Unknown variable name with space query

Astounding — Wed, 23 Aug 2017 13:53:49 GMT

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

Re: Unknown variable name with space query

Kurt_Bremser — Wed, 23 Aug 2017 14:00:21 GMT

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

Re: Unknown variable name with space query

RW9 — Wed, 23 Aug 2017 14:09:04 GMT

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.

Re: Unknown variable name with space query

ballardw — Wed, 23 Aug 2017 14:12:54 GMT

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.

Re: Unknown variable name with space query

Reeza — Wed, 23 Aug 2017 15:14:23 GMT

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.

Re: Unknown variable name with space query

PGStats — Wed, 23 Aug 2017 22:18:04 GMT

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.