Renaming permanent data sets using SAS PIPE

[SharonZS]

Hi All,

I'm renaming several sets of permanent SAS data sets using a SAS pipe -- which I'm still learning how to use...

Each set of of data sets has a non-continuous index (e.g causal3adj99rep2_1,  causal3adj99rep2_9, causal3adj99rep2_14...).

I'm  renaming the data sets with a continuous index that goes from 101 to 121 (causal3adj99_101  - causal3adj99_121).

I used a SAS pipe to create a list of all of the permanent SAS data sets that I need to rename (outputs a data set list with all of the names of my specified permanent data sets). I then created my new names & index for each of the data sets. I now have no idea how to link the new dat set names to the permanent SAS data sets in my dirrectory. Does anyone hafve any suggestions or example syntax?  

Here's my syntax thus far:

Filename DIRLIST1 pipe 'dir "E:\AC estimates\causal_matrix_rep2\causal_matrix_output7_Rep2\causal3adj99rep2_*.sas7bdat" /b ';

/* Creates list of permanent data sets to be renamed*/

data dirlist1 ;

   infile dirlist1;

   input file_name $19.;

run;

data dirlist1;

set dirlist1;

/* Breaks names of data sets into 2 parts-- retaining first part and replacing second*/

firstpart=substr(file_name,1,12);

strat=substr(file_name,18,2);

stratnum=strat+0;

run;

proc sort data=dirlist1;

by stratnum;

run;

data dirlist1;

set dirlist1;

num=_n_;

index=100+ num;

file_new=catx('_', firstpart, index);

run;

I'll be very appreciative of any suggestions anyone may have for how to best attach the new series of names to my permanent SAS data sets. Also wondering if this question might be better suited for one of the other forums?

Many thanks!

[SASKiwi]

How about using SAS DICTIONARY tables? I think you will find it easier:

libname MyLib 'E:\AC estimates\causal_matrix_rep2\causal_matrix_output7_Rep2';

proc sql;
  create table table_list as 
  select libname, memname
  from dictionary.members
  where upcase(libname) = "MYLIB";
quit;

[Reeza]

In this particular example you're better off not using pipes. Here's a single pass solution, which uses PROC DATASETS to change the name. This way, only the name changes and you don't need to recreate any files. For larger files this can be a significant difference in time. 

This passes the command to CALL EXECUTE which puts it together and it doesn't run until the end of the data step. If you're having trouble getting something like this to work, first make it a data set (remove _null_ and provide a name), make sure the new name and name are correct in the data set. Once that's done, this will work. 

data _null_;
 set sashelp.vtable 
    (where=(upcase(libname)='WORK')) end=eof;
 *filter list for only tables of interest;
 
 
 *start proc datasets;
 if _n_=1 then
    call execute ('proc datasets lib=WORK nodetails nolist; change');
    
 *add on new name calculation;
 new_name=catt('Name', put(_n_, z3.));
 
 *pass new and old name to proc datasets;
 call execute (memname);
 call execute ('=');
 call execute (new_name);
 
 *if last record then quit;
 If eof then
    call execute (';run;quit;');
run;

---

@SharonZS wrote:

Hi All,

I'm renaming several sets of permanent SAS data sets using a SAS pipe -- which I'm still learning how to use...

Each set of of data sets has a non-continuous index (e.g causal3adj99rep2_1,  causal3adj99rep2_9, causal3adj99rep2_14...).

I'm  renaming the data sets with a continuous index that goes from 101 to 121 (causal3adj99_101  - causal3adj99_121).

I used a SAS pipe to create a list of all of the permanent SAS data sets that I need to rename (outputs a data set list with all of the names of my specified permanent data sets). I then created my new names & index for each of the data sets. I now have no idea how to link the new dat set names to the permanent SAS data sets in my dirrectory. Does anyone hafve any suggestions or example syntax?  

Here's my syntax thus far:

Filename DIRLIST1 pipe 'dir "E:\AC estimates\causal_matrix_rep2\causal_matrix_output7_Rep2\causal3adj99rep2_*.sas7bdat" /b ';

/* Creates list of permanent data sets to be renamed*/

data dirlist1 ;

   infile dirlist1;

   input file_name $19.;

run;

data dirlist1;

set dirlist1;

/* Breaks names of data sets into 2 parts-- retaining first part and replacing second*/

firstpart=substr(file_name,1,12);

strat=substr(file_name,18,2);

stratnum=strat+0;

run;

proc sort data=dirlist1;

by stratnum;

run;

data dirlist1;

set dirlist1;

num=_n_;

index=100+ num;

file_new=catx('_', firstpart, index);

run;

I'll be very appreciative of any suggestions anyone may have for how to best attach the new series of names to my permanent SAS data sets. Also wondering if this question might be better suited for one of the other forums?

Many thanks!

---

[learsaas · Accepted solution]

Filename DIRLIST1 pipe 'dir "E:\AC estimates\causal_matrix_rep2\causal_matrix_output7_Rep2\causal3adj99rep2_*.sas7bdat" /b  /O:N';
option noxwait noXSYNC;
data _null_;
	infile dirlist1 length=len;
	input  file_name  $varying256. len;
	n=100+_n_;
	call system('rename "E:\AC estimates\causal_matrix_rep2\causal_matrix_output7_Rep2\'||trim(file_name)||'" causal3adj99rep2_'||strip(n)||'.sas7bdat');
run;
Filename DIRLIST1 clear;

[SharonZS]

Hi Learsaas,

I've been using this SAS pipe syntax to renumber my data sets.  Thank you! Works beautifully. The one detail that I haven't figured out is how to specify the sequence for the original numbers so that order is preserved in the new index. For example, in 1999 I might have original numbers: 3,5,7,27, 63 which I would like to retain in sequence so that my new data set names will be 101 for 3; 102 for 5; 103 for 7.... Is there a way to do this?  Current seems to renumber them randomly. Will appreciate any suggestions that you or any one else may have.  Thank you! 

[Reeza]

Add another step. So in the first step you read in the data and get the prefix/numbers of the old data sets. Sort that according to the order you want, and then do the RENAME in the next step.

[SharonZS]

Hi Reeza,

Thank you for your response. I've been trying to do exactly that and can't seem to get the syntax right. Still trying to master working with data set names rather than variables in data sets. I'm trying to create a variable for the original rep number from the name of the data file and then sort by that. I think that I'm almost there!  The data set of filenames is obviously being sorted, just not in in the order that I'd like.

When I sort using the syntax below, I'm now getting: 101 for orep= 1; 102 for orep= 14; 103 for orep= 18; 104 for 2; 105 for 22.  .  It's sorting by cycling the first digits by the order of the second digit (cycling through all of the 1s in first position and then all the 2s...) rather than treating the number as a whole  

I want: 101 for orep=1; 102 for orep=2; 103 for orep=14; 104 for  22....

Here's the syntax that I've been using:

Filename DIRLIST1 pipe 'dir "E:\AC estimates\causal_tables1_78c\Rep2Files\causaladj00rep2_*.sas7bdat" /b /O:N';
option noxwait noXSYNC;
data dirlist1;
infile dirlist1 length=len;
input file_name $varying256. len;
run;
data dirlist1;
set dirlist1;
orep=substr(file_name,17,2);
n=100+_n_;
run;
proc sort data=dirlist1;
by orep;
run;
data _null_;
set dirlist1;

call system ('rename "E:\AC estimates\causal_tables1_78c\Rep2Files\'||trim(file_name)||'" causal3adj00_'||strip(n)||'.sas7bdat');
run;
Filename DIRLIST1 clear;

[Reeza]

It's because it's numeric but you have it stored as a character.

data dirlist1;
infile dirlist1 length=len;
input file_name $varying256. len;
run;
data dirlist1;
set dirlist1;
orep=substr(file_name,17,2);
n=100+_n_;
run;

Instead, you can convert it to a  number using INPUT()

orep=input (substr(file_name,17,2), 8.);

Please use the code blocks when inserting code, it makes it easier to read and copy/paste. 

---

@SharonZS wrote:

Hi Reeza,

Thank you for your response. I've been trying to do exactly that and can't seem to get the syntax right. Still trying to master working with data set names rather than variables in data sets. I'm trying to create a variable for the original rep number from the name of the data file and then sort by that. I think that I'm almost there!  The data set of filenames is obviously being sorted, just not in in the order that I'd like.

When I sort using the syntax below, I'm now getting: 101 for orep= 1; 102 for orep= 14; 103 for orep= 18; 104 for 2; 105 for 22.  .  It's sorting by cycling the first digits by the order of the second digit (cycling through all of the 1s in first position and then all the 2s...) rather than treating the number as a whole  

I want: 101 for orep=1; 102 for orep=2; 103 for orep=14; 104 for  22....

Here's the syntax that I've been using:

Filename DIRLIST1 pipe 'dir "E:\AC estimates\causal_tables1_78c\Rep2Files\causaladj00rep2_*.sas7bdat" /b /O:N';
option noxwait noXSYNC;
data dirlist1;
infile dirlist1 length=len;
input file_name $varying256. len;
run;
data dirlist1;
set dirlist1;
orep=substr(file_name,17,2);
n=100+_n_;
run;
proc sort data=dirlist1;
by orep;
run;
data _null_;
set dirlist1;

call system ('rename "E:\AC estimates\causal_tables1_78c\Rep2Files\'||trim(file_name)||'" causal3adj00_'||strip(n)||'.sas7bdat');
run;
Filename DIRLIST1 clear;

---

[SharonZS]

Thank you!

[ScottBass]

---

@SharonZS wrote:

Hi Learsaas,

I've been using this SAS pipe syntax to renumber my data sets.  Thank you! Works beautifully. The one detail that I haven't figured out is how to specify the sequence for the original numbers so that order is preserved in the new index. For example, in 1999 I might have original numbers: 3,5,7,27, 63 which I would like to retain in sequence so that my new data set names will be 101 for 3; 102 for 5; 103 for 7.... Is there a way to do this?  Current seems to renumber them randomly. Will appreciate any suggestions that you or any one else may have.  Thank you! 

---

Assuming you're happy with the dataset name order as displayed in the SAS libname, then change my code as:

* create a metadata dataset using dictionary.tables ;
proc sql;
   create table metadata as
   select memname as oldname
         ,cats("MY_",memname,"_",100+monotonic()) as newname
   from   dictionary.tables
   where  libname="WORK"
   order by memname
   ;
quit;

[ScottBass]

Renaming via PIPE is a really bad idea.  Try doing this with a SPDE engine dataset 😉  Or an indexed dataset.  I strongly advise you to use PROC DATASETS instead.

Here is another approach from those already posted using my %loop_control utility macro.  See https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas. 

options mprint mrecall;

* create some sample datasets ;
options obs=0;
proc datasets lib=work kill nolist;
quit;
proc copy in=sashelp out=work;
 select s: z:;
run;
options obs=max;

* create a metadata dataset using dictionary.tables ;
proc sql;
 create table metadata as
 select memname as oldname
 ,cats("MY_",memname,"_",put(date(),yymmddn.)) as newname
 from dictionary.tables
 where libname="WORK";
quit;

* use the %loop_control macro ;
%macro code;
/*change &oldname=&newname; */
change %sysfunc(strip(&oldname))=%sysfunc(strip(&newname));
%mend;
proc datasets lib=work nolist;
%loop_control(control=metadata)
quit;

[SharonZS]

Thank you for your response! I'm going to study this and give it a try. PIPE was the first option I found when I was searching and trying to figure out how to do this.

[SharonZS]

Thank you so much for the replies! So appreciated! I'm going to be studying all three but am going to go with Learsaas' use of the SAS pipe because I have to run this syntax 42+ times for multiple years of data and sets of outcomes. Very succinct and gets the job done. 

Many thanks to all,

Sharon