Check for a variable when a joining the table inside proc sql ?

jjames1 — Wed, 19 Jul 2017 15:00:45 GMT

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;

Re: Check for a variable when a joining the table inside proc sql ?

Astounding — Wed, 19 Jul 2017 15:25:46 GMT

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 ','

Re: Check for a variable when a joining the table inside proc sql ?

ChrisNZ — Wed, 19 Jul 2017 22:37:56 GMT

Also the second,

SELECT cats('a.', name) I

should be

SELECT cats('b.', name) I

topic Check for a variable when a joining the table inside proc sql ? in SAS Programming

Check for a variable when a joining the table inside proc sql ?

Re: Check for a variable when a joining the table inside proc sql ?

Re: Check for a variable when a joining the table inside proc sql ?