topic Re: Dynamically changing variable column name that contains slash in New SAS User

Dynamically changing variable column name that contains slash

lyudmilpetrov — Fri, 19 Jul 2019 19:02:06 GMT

I have a script that loops through tables in a given library.

Once the table is grabbed there is a sub loop where it loops through column name and suppose to update them removing the "/" slashes.

All is good but does not read the columns properly when it reach such a column

the issue is in the line below where

&currentvar = table name dynamically picked.

&next_value. = column name original for example "ABC/D"

&yt = new column name that is "ABCD"

set LIBREF.&currentvar. (rename= (&next_value. = &yt.));

but it does not work!

It will work if you manually typed

set LIBREF.XYZ (rename= ('ABC/D'n = 'ABCD'));

All the best and much appreciated any help

Script below:

/* Step I */
LIBNAME libref 'F:\FOLDERWITHTABLES';
RUN;

/* Step II */

%let target_lib = 'LIBREF';

proc sql noprint;
select memname into :varlist separated by ' '
from dictionary.tables
where libname=&target_lib.
and memtype="DATA" and memname="TABLENAMEPICKED";
%let n=&sqlobs;
quit;
%MACRO abc;
/* First loop picking table names */
%do i=1 %to &n;
%let currentvar = %scan(&varlist,&i);

/* Second sub loop picking column names */
proc contents data=LIBREF.&currentvar. out=contents noprint; run;
proc sql;
select name into :varlist2 separated by ' ' from contents;
%let n2=&sqlobs;
quit;

%local i2 next_value x;
%let i2=1;
%do %while (%qscan(&varlist2, &i2, ' ') ne );
%let next_value = %qscan(&varlist2, &i2, ' ');
data _null_;
/*%let x = call symputx('x', translate(&next_value,'','/'));*/
%put &next_value || ' - ' || &i2;
/*call symputx('yt', translate(&next_value.,'_','/'));*/
%let yt = %sysfunc(translate(&next_value.,'_','/'));
%let counter = %sysfunc(findw(&next_value.,'/'));
%put &next_value || ' - ' || &i2 || ' - ' || &yt || ' - ' || &counter;

/* conversion */

data LIBREF.&currentvar.;
set LIBREF.&currentvar. (rename= (&next_value. = &yt.));
run;

run;

%let i2 = %eval(&i2 + 1);
%end;

%end;
%MEND abc;
%abc;

Re: Dynamically changing variable column name that contains slash

imvash — Fri, 19 Jul 2019 19:42:18 GMT

My suggestion is to put this before your macro call:

options mprint;

This helps you to see the generated code by your macro abc in the log. Once you see the generated code, you can debug easier.

Re: Dynamically changing variable column name that contains slash

Tom — Fri, 19 Jul 2019 20:23:39 GMT

You can eliminate the looping and just generate the OLD=NEW pairs from the metadata.

For example here is code that just generates PROC DATASETS code to rename the variables in place.

%macro abc(libref);
proc contents data=&libref.._all_ noprint 
  out=contents(keep=libname memname name) 
;
run;

data _null_;
  set contents;
  by libname memname ;
  where indexc(name,'/');
  if first.libname then call execute(cats('proc datasets nolist lib=',libname,';'));
  if first.memname then call execute(catx(' ','modify',memname,';'));
  call execute(catx(' ','rename',catx('=',nliteral(name),nliteral(compress(name,'/')),';'));
  if last.memname then call execute('run;');
  if last.libname then call execute('quit;');
run;
%mend abc;

libname mylib 'path to my datasets';
%abc(mylib);

You could modify it to generate new datasets if what instead.

Re: Dynamically changing variable column name that contains slash

ballardw — Mon, 22 Jul 2019 21:15:06 GMT

Proximate cause is likely that TRANSLATE is not doing what you think it is:

data example;
   x='ABC/D';
   y=translate(x,'','/');
run;

Note that the Y value has a space where the / was. So the value of &yt is not a valid variable name.

So consider this code

data example;
   x='ABC/D';
   y=compress(translate(x,'','/'));
run;

A third piece is there isn't anything where you are building a string that looks like the "ABC/D"n. The name as stored in the dictionary table is simply ABC/D. You have to work to get a proper name-literal value.

And last, macro coding per se is not needed. You can create a data set with all of the data set names where the variable(s) have / in the name and then use a data step to either write Proc Datasets code or use call execute to create lines of code for proc data sets.

proc sql;
  create table work.rename as 
  select memname,  cats(quote(strip(name)),'n') as name,
  compress(translate(name,' ','/')) as newname
  from dictionary.columns
  where libname=upcase("&target_lib.")
      and index(name,'/')>0
  order by memname
  ;
quit;

data _null_;
   set work.rename end=LastName;
   by memname;
   if _n_ = 1 then 
      Call execute ("Proc datasets library=&target_lib. nodetails nolist;");
   if first.memname then do;
      Call execute ("modify "||memname||";");
      Call execute ("rename ")  ;
   end;
   Call execute(catx(' ',name,' = ',newname)) ;
   if LastName then do;
      Call execute (";") ;
      Call execute ("quit;");
   end;
run;

Proc datasets can change variable names, formats or labels for a data set or variables. When you use a data step for that purpose you have to read the entire data which could take significant time, not mention in this case if you have multiple problem variables you reread that set for each variable, terribly inefficient.

Re: Dynamically changing variable column name that contains slash

Tom — Fri, 19 Jul 2019 20:21:29 GMT

Make sure to use delimiters in your lists of names that do not appear in the names. If your names contain / then they might also contain spaces.

Re: Dynamically changing variable column name that contains slash

lyudmilpetrov — Fri, 19 Jul 2019 20:26:37 GMT

Hi Tom,

Thank you so much,

Reading that it seems super genius, I just started using SAS few days ago and still learning but have experience in other languages, so still learning the ways of SAS.
I will try it later and let you know if all good.

All the best,

Lyudmil

Re: Dynamically changing variable column name that contains slash

Tom — Fri, 19 Jul 2019 23:36:36 GMT

@lyudmilpetrov wrote:
Hi Tom,

Thank you so much,

Reading that it seems super genius, I just started using SAS few days ago and still learning but have experience in other languages, so still learning the ways of SAS.
I will try it later and let you know if all good.

All the best,

Lyudmil

Learn how to use SAS before trying to do things with the macro language. It is really hard to write a program that generates code when you don't know what code you should be generating.

For example where did those goofy names come from? Perhaps they were column headers on a file you imported? If you set the options VALIDVARNAME to V7 before the PROC IMPORT step then it should convert that strange headers into valid variable names for you automatically and there is nothing to rename.

Re: Dynamically changing variable column name that contains slash

Reeza — Sat, 20 Jul 2019 00:14:54 GMT

You don't want to rename within a data step, that processes all the data again. SAS doesn't know how or what you're changing so it basically creates an entirely new data set. Instead, use PROC DATASETS which will just update the name alone and that takes a fraction of the time.

Re: Dynamically changing variable column name that contains slash

Patrick — Sat, 20 Jul 2019 10:11:59 GMT

@lyudmilpetrov

If you've got a programming background then reading through "SAS® 9.4 Language Reference: Concepts, Sixth Edition" is likely what will you fastest familiarise with the SAS intrinsics.

https://go.documentation.sas.com/?docsetId=lrcon&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en

Re: Dynamically changing variable column name that contains slash

Patrick — Sat, 20 Jul 2019 10:12:43 GMT

Reading that it seems super genius, I just started using SAS few days ago and still learning but have experience in other languages, so still learning the ways of SAS.

@lyudmilpetrov

If you've got a programming background then reading through "SAS® 9.4 Language Reference: Concepts, Sixth Edition" is likely what will you fastest familiarise with the SAS intrinsics.

https://go.documentation.sas.com/?docsetId=lrcon&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en

Re: Dynamically changing variable column name that contains slash

lyudmilpetrov — Mon, 22 Jul 2019 18:08:54 GMT

Hi Tom,

Thanks for the shared knowledge I run it and it produces the following error

79
45 ! last.libname then call execute('quit

ERROR 79-322: Expecting a ).

I am not sure why is that.

Thank you,

Re: Dynamically changing variable column name that contains slash

lyudmilpetrov — Mon, 22 Jul 2019 18:37:43 GMT

Thanks a lot, I accepted your response as solution works perfectly.

All the best,

Lyudmil

Re: Dynamically changing variable column name that contains slash

Tom — Mon, 22 Jul 2019 18:38:22 GMT

So fix it and rerun it.

It you want more help you need to show the full log of the code you actually ran. Make sure to paste using the Insert Code pop-up window so that formatting of the log is preserved.