Check the presence of variable when a join the table from within the proc sql before joining?

[jjames1]

Hello,

I have a final_table that I need to create after joining some other tables.

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.

My actual scenario is :

proc sql ;
	create table final_table  AS
		select
			
			 a.customer
			,a.year
			,a.var1
			,a.var2
			,a.var3
			,b.*
			,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;

But in my current XML var1,var2 and var3 are not present. And I get the following error

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

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

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

So I would like to know if I can check the presence of variable when a join the table from within the proc sql.

Please help

Thanks in advance!

[RW9]

Well, you can look at sashelp.vcolumn or dictionary.columns (same thing later is SQL syntax).  However the question is, how are you going to code for data you don't know?  What happens if its a different type, or needs further processing.  Step 1 about any programming with SAS is to know your data as SAS is a data programming language. 

[jjames1]

Hello,

Thank you for the quick reply

I have done the coding part for handling my exceptions for the different types of XMLs that I receive.

In some cases I dont have the value for this three variables so I would just like to know if I could do this during joining the table.

[collinelliot]

You can always just use "a.*" and get everything in the dataset, so it won't fail if those variables are not there. However, you might get more than you want and you'll need to be careful about naming conflicts with the other tables.

[jjames1]

Yes, I have a lot of variables if I use a.*

So filtering them out wont be very easy 😞

[collinelliot]

Is it always the same "var#" pattern that you want to check for, or are there specific variable names that may or may not be there?

You can always query the metadata to see if a variable or a set of variables is there and then inside of a macro conditionally execute that portion of the select statement. You could also create a macro variable from the metadata that would only have the variables actually present in your data and then use that in your code. For example:

proc sql noprint;
SELECT cats('a.', name) INTO :dynamicSelectVars
FROM sashelp.vcolumn
WHERE libname = 'WORK' and
               memname = 'FINAL_OUTPUT' and
               upcase(name)  eqt 'VAR';
quit;

You'll need to be careful if none of the variables are in there, as it won't generate a macro variable, so you might want to initialize it to null. But given the details you've given, that's one approach.

[jjames1]

yes there are specific variable names 

[jjames1]

Thank you for your reply.

I tried the following 

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

%put &dynamicSelectVars;
proc sql ;
	create table final_table  AS
		select
			
			 a.customer
			,a.year
			,&dynamicSelectVars
			,b.*
			,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;

 Here this code works when I have any of the variables a.x1 or a.y1 or a.z1

But if I dont have any of the variables this gives me the same error that I have shown in my original post.

So can you help on how to handle this exception?

Thankl you !!

[collinelliot]

For one, the following needs to be addressed:

               upcase(name)  in  ('x1','y1','z1');

Should be: 

               upcase(name)  in  ('X1','Y1',Z1');

Or make it lowcase, not upcase. 

If none of those exist in the data set, then the macro variable will not be created, so you might want to initialize the macro variable to null before you try to create it:

%let dynamicSelectVars =;

[collinelliot]

The leading comma will also be an issue in the event the macro variable is null, too. 

[collinelliot]

I'm not sure if you're still dealing with this, but an approach that will resolve the issue with null macro variables and trailing commas is just to query all of your "a" vars from metadata. You know that customer and year will always be there, so the only question is the others:

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

%put &dynamicSelectVars;
proc sql ;
	create table final_table  AS
		select &dynamicSelectVars
			, b.*
			, 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;

[jjames1]

Thank you so much for your reply

What If I need to check the presence of variables and arrange them in my final_output?

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;

[collinelliot]

If the final order of the variables in the table is that critical, the solution is going to get more complicated/convoluted. Splitting into two separate queries to separate those variables will cause issues when one of them returns a null value. What I would do is use my last approach and then use the metadata on your final table to pull all the variables into a single macro variable ordered as you want. But again, this is going to get fairly convoluted to get the exact ordering you want.