Extract part of a variable name

[Moksha]

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

[yabwon]

how about:

data have;
input name $ : 32. @@;
cards;
EXSTDTC LBDTC LBDTC LBDTC LBDTC
run;

data want;
set have;
name = substr(name, 1, length(name)-1);
run;

Bart

[yabwon]

Or other option:

data have;
input name $ : 32. @@;
cards;
EXSTDTC LBDTC LBDTC LBDTC LBDTC EXSTDTC12PQ
run;

data want;
set have;
name = tranwrd(upcase(name), "DTC", "DT");
run;
proc print;
run;

Bart

[Moksha]

Thank you very much. But, in a data step, it's working for me. But, the same code inside a macro is not working. I need to resolve the issue in the macro. I need this macro. Can you please suggest?

[PaigeMiller]

@Moksha I point out again that saying "it doesn't work" doesn't help, without further information. We need to see the code you are using. If there are errors in the log, show us the ENTIRE log. If there are no errors in the log but you are getting the wrong output, explain and show us the code and the incorrect output.

[Moksha]

I have already provided the code that I am using  and log. Can you please check again?

[PaigeMiller]

Repeating:

If there are errors in the log, show us the ENTIRE log. If there are no errors in the log but you are getting the wrong output, explain and show us the code and the incorrect output.

[Moksha]

This is the entire log that I have provided already. In this, now I have highlighted the Error in Red color.

I have rechecked many times but the closing parenthesis is there. 

 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")

[Tom]

In addition to the SQL query making no sense the macro logic makes no sense.

Let's look at it:

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

You don't tell %QSCAN() what delimiter to use.  So make sure that none of the other default delimiters it will use appear in your names.

Why are you using %SYSFUNC() to call the SAS function SUBSTR() instead of just using the actual macro function %SUBSTR()?

Why are you inserting text string cats( and ) into the value of the macro variable NEWNAME?  Are you planning to use this macro to generate a call to the CATS() function?  To concatenate strings in macro code just TYPE them next to each other.

Try this instead:

%do i = 1 %to &cnt;
 %let var = %qscan(&varnames, &i,%str( ));
 %let newname = %substr(&var,1,%length(&var)-3)&str1 ;
 %put &=i &=var &=newname ;
%end;

Example:

851   %let var=xxxDTC;
852   %let str1=DT;
853   %let newname = %substr(&var,1,%length(&var)-3)&str1 ;
854   %put &=var &=newname ;
VAR=xxxDTC NEWNAME=xxxDT

[Moksha]

Thank you very much for providing insights as what can be done better. I will definitely use these points going forward.

[Tom · Accepted solution]

Manipulate data in CODE.  Leave the macro code for code GENERATION.

First thing is it makes no sense to query more than one dataset for this issue.  What are you going to do with a list of variables that come from different datasets?

Second no need to use space as the delimiter.  That will just make using macro code harder later.

So something like this will make four macros with the names of the "date" variables that are character from the dataset PRACTICE.DATASET.

proc sql NOPRINT:
select name
     , substr(name,1,length(name)-3)
     , tranwrd(upcase(name),'DTC ','DT')
 into :original separated by '|'
    , :base separated by '|'
    , :new separated by '|'
from dictionary.columns
where libname='PRACTICE'
   and memname='DATASET'
   and type='char' 
   and upcase(name) like '%DTC'
   and (find(name,'dat','i') or find(label,'dat','i'))
;
%let nvars=&sqlobs;
quit;

So you might get 

%let original=EXSTDTC|LBDTC;
%let base=EXST|LB;
%let new=EXSTDT|LBDT;
%let nvars=2;

Which perhaps you then use within a macro to generate some code:

data want;
  set practice.dataset;
%do i=1 %to &nvars;
  %scan(&new,&i,|) = input(%scan(&original,&i,|),yymmdd10.);
  format %scan(&new,&i,|) yymmdd10. ;
%end;
run;

[Moksha]

Thank you very much. Your inputs helped to move further.

[Kurt_Bremser]

Extract the part in the SQL step:

proc sql;
select substr(name,1,length(name)-3) into :varnames separated by ' '
from dictionary.columns
where libname=upcase("practice") and type='char' and memtype='DATA' and (index(upcase(name),"DTC")>0;
quit;

Then use this like this:

%let varpart = %scan(&varnames.,&i.);
%let var = &varpart.dtc;
%put varpart is ..... &varpart.;
%let newname = &varpart.&str1.;
%put new name is ..... &newname.;

[Moksha]

Thank you very much.