topic Concatenation of Dynamically Generated Macro into a new Macro Variable in SAS Programming

Concatenation of Dynamically Generated Macro into a new Macro Variable

singhsahab — Sun, 05 Apr 2020 13:47:05 GMT

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

🙂

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

PaigeMiller — Sun, 05 Apr 2020 13:57:08 GMT

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

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

singhsahab — Sun, 05 Apr 2020 14:05:31 GMT

@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;

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

Tom — Sun, 05 Apr 2020 14:10:46 GMT

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?

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

singhsahab — Sun, 05 Apr 2020 14:17:12 GMT

@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

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

PaigeMiller — Sun, 05 Apr 2020 17:15:28 GMT

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

Re: Concatenation of Dynamically Generated Macro into a new Macro Variable

Tom — Sun, 05 Apr 2020 19:45:30 GMT

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