BookmarkSubscribeRSS Feed
fabdu92
Obsidian | Level 7

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?

6 REPLIES 6
Astounding
PROC Star

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
Super User

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Super User
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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1254 views
  • 2 likes
  • 7 in conversation