Hi All, I have queried the dictionary.columns for char type date variable names. The output variables are something like EXSTDTC LBDTC LBDTC LBDTC LBDTC. From each of these variable names, I have to extract the name till the part where "DTC" starts i.e., I want to extract EXST from first variable, LB from second variable, LB from third variable etc.,. Then, I want to create new variables like EXSTDT, LBDT, LBDT so on. Please, advise how to correct it. I am trying with the following code, but getting errors: Code: %macro have; proc sql; select name into :varnames separated by ' ' from dictionary.columns where libname=upcase("practice") and type='char' and memtype='DATA' and (index(upcase(name),"DAT")>0 or index(upcase(label),"DAT")>0); quit; %let str1 = "DT"; %let cnt = %sysfunc(countw(&varnames)); %do i = 1 %to &cnt; %let var = %qscan(&varnames, &i); %let varpart = %sysfunc(substr(&var,1,%sysfunc(index(&var,"DTC")-1))); %put varpart is ..... &varpart; %let newname = cats(&varpart,&str1); %put new name is ..... &newname; %end; %mend have; options mprint symbolgen; %have; options mprint symbolgen;
244 %have;
MPRINT(HAVE): proc sql;
MPRINT(HAVE): select name into :varnames separated by ' ' from dictionary.columns where
libname=upcase("practice") and type='char' and memtype='DATA' and (index(upcase(name),"DAT")>0
or index(upcase(label),"DAT")>0);
MPRINT(HAVE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable CNT resolves to 5
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable VAR resolves to EXSTDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable VAR resolves to EXSTDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
ERROR: Expected close parenthesis after macro function invocation not found.
SYMBOLGEN: Macro variable VARPART resolves to E)
varpart is ..... E)
SYMBOLGEN: Macro variable VARPART resolves to E)
SYMBOLGEN: Macro variable STR1 resolves to "DT"
SYMBOLGEN: Macro variable NEWNAME resolves to cats(E),"DT")
new name is ..... cats(E),"DT")
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
ERROR: Expected close parenthesis after macro function invocation not found.
SYMBOLGEN: Macro variable VARPART resolves to L)
varpart is ..... L)
SYMBOLGEN: Macro variable VARPART resolves to L)
SYMBOLGEN: Macro variable STR1 resolves to "DT"
SYMBOLGEN: Macro variable NEWNAME resolves to cats(L),"DT")
new name is ..... cats(L),"DT")
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable I resolves to 3
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
ERROR: Expected close parenthesis after macro function invocation not found.
SYMBOLGEN: Macro variable VARPART resolves to L)
varpart is ..... L)
SYMBOLGEN: Macro variable VARPART resolves to L)
SYMBOLGEN: Macro variable STR1 resolves to "DT"
SYMBOLGEN: Macro variable NEWNAME resolves to cats(L),"DT")
new name is ..... cats(L),"DT")
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable I resolves to 4
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
ERROR: Expected close parenthesis after macro function invocation not found.
SYMBOLGEN: Macro variable VARPART resolves to L)
varpart is ..... L)
SYMBOLGEN: Macro variable VARPART resolves to L)
SYMBOLGEN: Macro variable STR1 resolves to "DT"
SYMBOLGEN: Macro variable NEWNAME resolves to cats(L),"DT")
new name is ..... cats(L),"DT")
SYMBOLGEN: Macro variable VARNAMES resolves to EXSTDTC LBDTC LBDTC LBDTC LBDTC
SYMBOLGEN: Macro variable I resolves to 5
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable VAR resolves to LBDTC
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
ERROR: Expected close parenthesis after macro function invocation not found.
SYMBOLGEN: Macro variable VARPART resolves to L)
varpart is ..... L)
SYMBOLGEN: Macro variable VARPART resolves to L)
SYMBOLGEN: Macro variable STR1 resolves to "DT"
SYMBOLGEN: Macro variable NEWNAME resolves to cats(L),"DT")
new name is ..... cats(L),"DT") When I use the substr with index in a dataset it's working without errors. For example, data test1; var1 = "EXSTDTC12PQ"; newvar = substr(var1,1,index(var1,"DTC")-1); put "The new var is ...." newvar=; run; Output of this in the log : The new var is ....newvar=EXST
... View more
