07-19-2017 11:00 AM
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;
07-19-2017 11:25 AM
Look at what happens if all your variables are present. In the middle of the SELECT statement, SAS would see:
,x1 y1 z1
The correct syntax for SQL is probably this instead:
To make that happen change your separator in both places: SEPARATED BY ','