BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Moksha
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

13 REPLIES 13
yabwon
Meteorite | Level 14

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Meteorite | Level 14

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Moksha
Quartz | Level 8
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
Diamond | Level 26

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

--
Paige Miller
Moksha
Quartz | Level 8

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Moksha
Quartz | Level 8

 

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
Super User Tom
Super User

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
Quartz | Level 8

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Quartz | Level 8

Thank you very much.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 398 views
  • 0 likes
  • 5 in conversation