BookmarkSubscribeRSS Feed
jjames1
Fluorite | Level 6

Hello All,

I am posting this question with reference to the QUESTION that I have posted before. I am trying to solve a more complicated scenario this time. As said I am dealing with large number of different structured xmls, So everytime whenever I create my final_table I would like to know if there is a way that I can check the variable is present or not before doing the join inside the proc sql.

 

 

Here this code works when I have any of the variables [a.x1 or a.y1 or a.z1] for dynamicSelectVar1 and [a.x2 or a.y2] for dynamicSelectVar2

But if I dont have any of the variables this gives me the  error that I have shown in my original post. For instance if I dont have any variables in dynamicSelectVar2 then the error is:

ERROR: Column x2 could not be found in the table/view identified with the correlation name a.
ERROR: Column y2 could not be found in the table/view identified with the correlation name a.

So can you help on how to handle this exception

 

 

Following is my piece of code 

 

proc sql noprint;
SELECT cats('a.', name) INTO :dynamicSelectVar1 separated by ' '
FROM sashelp.vcolumn
WHERE libname = 'WORK' and
               memname = 'FINAL_OUTPUT' and
               name  in  ('x1','y1','z1');
quit;
%put &dynamicSelectVar1;

proc sql noprint;
SELECT cats('a.', name) INTO :dynamicSelectVar2 separated by ' '
FROM sashelp.vcolumn
WHERE libname = 'WORK' and
               memname = 'FINAL_OUTPUT' and
               name  in  ('x2','y2');
quit;
%put &dynamicSelectVar2;


proc sql ;
	create table final_table  AS
		select
			 a.customer
			,a.year
			,&dynamicSelectVar1
			,b.*
                        ,&dynamicSelectVar2
			,c.*
			
	from work.final_output as a 
        inner join prod.scores as b on a.id = b.id
	inner join prod.city as c on a.business_id=c.id
	
quit;

 

2 REPLIES 2
Astounding
PROC Star

Look at what happens if all your variables are present.  In the middle of the SELECT statement, SAS would see:

 

,a.year

,x1 y1 z1

,b.*

 

The correct syntax for SQL is probably this instead:

 

,a.year

,x1,y1,z1

,b.*

 

To make that happen change your separator in both places:  SEPARATED BY  ','

ChrisNZ
Tourmaline | Level 20

Also the second, 

SELECT cats('a.', name) I

should be

SELECT cats('b.', name) I

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 716 views
  • 1 like
  • 3 in conversation