Concatenation of Dynamically Generated Macro into a new Macro Variable

[singhsahab]

Hi All,

I want to concatenate dynamically generated macro variables into a new macro variable for my further use. As per my below code there are some variable are stored in my "Table1" dataset. I took a unique count into co variable and name of all variable in macro variable DATA_IN1 ,DATA_IN2.....etc. 

I have one more VVV dataset where some more variable are present as prefix name same as "Table1" . VVV tables contains more the one variable as the prefix . i want to take all the variable name from VVV tables which was present in Table1 with prefix name.

Ex: In table1 - two variable like - name  address

      In VVV table - variable name like -name name_middle name_last address_Pin address_code 

Output needed into new macro variable as : new_macro_var = name name_middle name_last address_Pin address_code 

%MACRO Concat_Macro;
%DO I=1 %TO &CO.;
PROC SQL NOPRINT;
SELECT NAME INTO :IN&I. SEPARATED BY ' ' FROM VVV WHERE NAME LIKE "&&DATA_IN&I..%";
QUIT;
%PUT &&IN&I.;
%END;
%MEND;

I'm not sure which statement will help me to concatenate it. Above is my sample code . It didn't contain concatenate details . 

Thank you in advance !!

🙂 

[PaigeMiller]

Please show us a portion of the data set(s) you are talking about.

[singhsahab]

@PaigeMiller 

 Below is the data portion . Please Note . I don't want to merge these two tables. I need to take variables name from VVV dataset based on table1 datset.

data Table1;
length name address $20.;
input name address;
datalines;
AAA Bangalore
BBB Delhi
;
run;

data Table1;
length name name_middle name_last $20.;
input name name_middle name_last address_Pin address_code;
datalines;
shy singh G 23130 91
kk kumar  G 45678 91
;
run;

[PaigeMiller]

---

@singhsahab wrote:

@PaigeMiller 

 Below is the data portion . Please Note . I don't want to merge these two tables. I need to take variables name from VVV dataset based on table1 datset.

data Table1;
length name address $20.;
input name address;
datalines;
AAA Bangalore
BBB Delhi
;
run;

data Table1;
length name name_middle name_last $20.;
input name name_middle name_last address_Pin address_code;
datalines;
shy singh G 23130 91
kk kumar  G 45678 91
;
run;

---

There is no VVV data set.

[Tom]

That is not at all clear, please show example input and expected output.

Why do you feel the need to take data from datasets put it into macro variables then use the macro variables to query other datasets? Why not just combine the two datasets directly and skip the translates to/from text required to but the data into macro variables?

[singhsahab]

@Tom Here is my code 

data Table1;
length name address $20.;
input name address;
datalines;
AAA Bangalore
BBB Delhi
;
run;

data Table12;
length name name_middle name_last $20.;
input name name_middle name_last address_Pin address_code Area_code area_pin;
datalines;
shy singh G 23130 91 12 2
kk kumar  G 45678 91 23 1
;
run;

PROC SQL NOPRINT;
SELECT COUNT(DISTINCT(name)) INTO :CO FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE1'; 
SELECT DISTINCT(name) INTO :DATA_IN1 - :DATA_IN%LEFT(&CO) FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE1';
CREATE TABLE VVV AS 
SELECT NAME  FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE12';
RUN;


/*OPTIONS MPRINT MLOGIC SYMBOLGEN;*/
%MACRO Concat_Macro;

%DO I=1 %TO &CO.;

PROC SQL NOPRINT;
SELECT NAME INTO :IN&I. SEPARATED BY ' ' FROM VVV WHERE NAME LIKE "&&DATA_IN&I..%";
QUIT;
%PUT &&IN&I.;
%END;
%MEND;

%Concat_Macro;


--I want only "name name_middle name_last address_Pin address_code" varibale from table12,if Prefiex text is present in Table1, extra variable i don't want to take .
--I have one more macro where i need to pass all these variable dynamically through one macro variable so i can get final output as below 

name name_middle name_last address_Pin address_code
shy singh G 23130 91 
kk kumar  G 45678 91 

[Tom · Accepted solution]

Your explanation seems to have skipped a few tracks on the record.

Reading between the lines it looks like you want NAMES of the variables in TABLE12 whose NAMES are extensions of the NAMES of the variables in TABLE1.

proc sql noprint ;
select distinct a.varnum, a.name 
  into :dummy , :namelist separated by ' '
  from dictionary.columns a
     , dictionary.columns b
  where a.libname="WORK" and a.memname="TABLE12"
    and b.libname="WORK" and b.memname="TABLE1"
    and upcase(a.name) like upcase(cats(b.name,'%'))
  order by 1
;
%let n=&sqlobs;
quit;

881   %put &=n &=namelist;
N=5 NAMELIST=name name_middle name_last address_Pin address_code