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
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.
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.