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; ... View more

Hi, I am getting below error when I try to run attached code. There is some issue with data type.How can it be rectified in the case step   ERROR: Result of WHEN clause 3 is not the same data type as the preceding results. data one; infile datalines missover; input id specifier $ ; datalines; 01 class1 02 123 03 3456 04 aaaa 05 N/A 06 ; run; proc sql; create table two as select id, case when specifier is missing then "unspecified" when specifier="aaaa" or specifier="N/A" then "12345" when length(specifier)=3 then specifier=cats(0,specifier) else specifier end as specifier_new from one; quit;   ... View more

Hi, I have tried this..But it is not giving me desired output.Might be some complex programming is required for my requirement to be fulfilled. Requirement is "If I have multiple Types for an ID ,then Type must be concatenated to ID field. But, if we have only one type for an ID , then type need not be concatenated with ID field.Here,Jan has multiple types (Class1 and class2) .Hence, type must be concatenated with ID like Janclass1 ,Janclass2. But for feb and mar , concatenation need not happen since they have only one value for type ( class3 type for feb and  class4 type for mar).   The expected output should have following ID values : janclass1 , janclass1,janclass2,feb,feb,mar Please do not use Origin field in the logic to acheive this.         data one; input ID$ Type$ origin$; datalines; jan class1 UK jan class1 UAE jan class2 US feb class3 INDIA feb class3 MIDEAST mar class4 NORWAY ; run; Proc sql; create table temp as Select ID,Type,count(*) as N from one group by ID,Type; quit; data two; set temp; if N=1 then ID=cats(ID,Type); run;   ... View more

I have two columns id,type Each Id can have more than one type. Whenever Id has more than one type, type has to be concatenated with ID. When ID has only one type ,then type should not be concatenated with ID Sample input data ID Type 1 A 1 B 2 A 3 D Output expected : ID Type 1A A 1B B 2 A 3 D ... View more