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:
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;
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 ','
Also the second,
SELECT cats('a.', name) I
should be
SELECT cats('b.', name) I
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.