topic Re: Extract part of a variable name in SAS Programming

Extract part of a variable name

Moksha — Mon, 29 May 2023 20:25:10 GMT

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

Re: Extract part of a variable name

yabwon — Mon, 29 May 2023 20:30:43 GMT

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

Re: Extract part of a variable name

yabwon — Mon, 29 May 2023 20:32:41 GMT

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

Re: Extract part of a variable name

Moksha — Mon, 29 May 2023 20:43:44 GMT

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?

Re: Extract part of a variable name

PaigeMiller — Mon, 29 May 2023 20:52:09 GMT

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

Re: Extract part of a variable name

Moksha — Mon, 29 May 2023 20:53:25 GMT

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

Re: Extract part of a variable name

PaigeMiller — Mon, 29 May 2023 20:54:22 GMT

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.

Re: Extract part of a variable name

Tom — Mon, 29 May 2023 21:14:41 GMT

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;

Re: Extract part of a variable name

Moksha — Mon, 29 May 2023 21:01:15 GMT

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

Re: Extract part of a variable name

Tom — Mon, 29 May 2023 21:12:13 GMT

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

Re: Extract part of a variable name

Kurt_Bremser — Mon, 29 May 2023 21:54:12 GMT

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

Re: Extract part of a variable name

Moksha — Tue, 30 May 2023 03:08:25 GMT

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

Re: Extract part of a variable name

Moksha — Tue, 30 May 2023 03:09:10 GMT

Thank you very much.

Re: Extract part of a variable name

Moksha — Tue, 30 May 2023 03:11:28 GMT

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