DATA Step, Macro, Functions and more

Unknown variable name with space query

Reply
Contributor
Posts: 45

Unknown variable name with space query

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?

Super User
Posts: 6,543

Re: Unknown variable name with space query

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

 

 

Super User
Posts: 9,611

Re: Unknown variable name with space query


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.

 

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,227

Re: Unknown variable name with space query

Totall agree with @KurtBremser 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.

Super User
Posts: 13,084

Re: Unknown variable name with space query

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.

 

 

Super User
Posts: 22,874

Re: Unknown variable name with space query

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.
Esteemed Advisor
Posts: 5,408

Re: Unknown variable name with space query

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.

PG
Ask a Question
Discussion stats
  • 6 replies
  • 105 views
  • 2 likes
  • 7 in conversation