Is it possible to use a sas macro variable after connecting to hadoop

[Sathya3]

Hi ,
I have to connect to hadoop from SAS using proc sql and combine HIVE tables using union all. I have to use HIVE QL
List of source tables are like t_cdr_202212_1,t_cdr_202212_2...so on till t_cdr_202212_n where n is not a fixed number and it is a sequential number from 1 upwards
HVGRP is schema where hive tables are stored .%connection_settings is a macro to connect to hadoop from SAS

problem here is :

"&query. "macro variable is not getting resolved in below part of code as it is sas macro.Is there any way I can use a sas macro variable after connecting to hadoop. I have attached full code as well for your reference

proc sql;
connect to hadoop as myconn(%connection_settings(HVGRP));
execute (
&query.
) by myconn;
disconnect from myconn;
quit;

data t_cdr_202212_1;
input name $;
datalines;
aaa
bbB
CCC
;
run;

data t_cdr_202212_2;
input name $;
datalines;
aa
bb
;
run;

data t_cdr_202212_10;
input name $;
datalines;
aa
bb
;
run;


%macro test;
  /* Create table_list using DICTIONARY.TABLES */
  proc sql;
    create table table_list as
    select memname
    from dictionary.tables
    where libname = 'hvgrp'
      and memname like 'T_CDR_202212_%';
  quit;

  /* Sort the table_list in the proper order */
  data table_list_sorted;
    set table_list;
    num_part = input(substr(memname, length('T_CDR_202212_')+1), 8.);
  run;

  proc sort data=table_list_sorted;
    by num_part;
  run;

  /* Calculate the value of 'n' */
  proc sql noprint;
    select count(*) into :n
    from table_list_sorted;
  quit;

  /* Construct the query */
  %let query = %str(create table t1 as);

  %do i = 1 %to &n;
    data _null_;
      set table_list_sorted;
      if _n_ = &i then call symputx('table_name', memname);
    run;
    
    %let query = %str(&query. select * from hvgrp.&&table_name);

    %if &i < &n %then %let query = %str(&query. union all);
  %end;

  %put &query;

  proc sql;
    connect to hadoop as myconn(%connection_settings(HVGRP));
    execute (
      &query.
    ) by myconn;
    disconnect from myconn;
  quit;
%mend test;


%test;

[Tom]

It should work fine, as long as the number of tables is small enough that the generated code fits in a single macro variable.

You don't need the %STR() as there is NOTHING in the code you generating that needs to have macro quoting.  In fact the macro quoting might be the cause of the error. What did the macro variable end up containing?

You don't need (or want) the double & before the TABLE_NAME macro variable reference.

You might want to adds quotes around the table name, just in case it has something strange like embedded spaces.

/* Construct the query */
  %let query=create table t1 as;
  %let sep;
  %do i = 1 %to &n;
    data _null_;
      set table_list_sorted (firstobs=&i obs=&i);
      call symputx('table_name', quote(strip(memname)));
    run;
    %let query = &query. &sep. (select * from hvgrp.&table_name);
    %let sep=union all;
  %end;
  %put &query;

[Sathya3]

create table t1 as union all (select * from hvgrp."T_CDR_202212_1") union all (select * from hvgrp."T_CDR_202212_2") union all

(select * from hvgrp."T_CDR_202212_10")

 query variable from the code I have posted gives correct result. But my problem is ,it is not getting resloved inside hadoop execute

proc sql;
connect to hadoop as myconn(%connection_settings(HVGRP));
execute (
&query.          /*This is  the place where query is not getting resolved since it is in hadoop I believe .I need a solution for             this.Earlier to this ,query is getting resolved as expected in sas */
) by myconn;
disconnect from myconn;
quit;

[Patrick · Accepted solution]

The macro variable resolves BEFORE the code gets sent to Hadoop. 

Because you use %str() when populating the macro var there might be some invisible quotes that get in the way. I've seen this happening with Oracle. 

Try %unquote(&query).