%do %to

[stella7]

I want to change the following code ,using %do %to, to be more efficient.

If the file name includes "bridge02_08" and then change the name  like "b _2008 ",    .....................

Removing the redundudnt part.

/**** Read all files in my directory ****************************/
filename ren pipe 'dir "C:\data\age_years\*"/b' ;
data ren;
infile ren;
input old : $250.;

/***** Need to change the file name because I call each file from the same sql code.

data ren_new ;
set ren;

old=tranwrd(old, "bridge02_08", "b_2008");
old=tranwrd(old, "bridge02_09", "b_2009");
old=tranwrd(old, "bridge02_10", "b_2010");
old=tranwrd(old, "bridge02_11", "b_2011");
old=tranwrd(old, "bridge02_12", "b_2012");
old=tranwrd(old, "bridge02_13", "b_2013");
old=tranwrd(old, "bridge02_14", "b_2014");
old=tranwrd(old, "bridge02_15", "b_2015");
old=tranwrd(old, "bridge02_16", "b_2016");
old=tranwrd(old, "bridge02_17", "b_2017");
old=tranwrd(old, "bridge02_18", "b_2018");
old=tranwrd(old, "bridge02_19", "b_2019");
old=tranwrd(old, "bridge02_20", "b_2020");
old=tranwrd(old, "bridge02_21", "b_2021");
old=tranwrd(old, "bridge02_22", "b_2022");

put old;

run;

[Quentin]

It looks like you might be able to do it with just one statement:

old=transtrn(old, "bridge02_", "b_20");

?

[stella7]

I need to put up year for each bridge01_08a   for year 2008  ,  bridge01_09a for y2009..... These are  file names, so I call  each file name inside proc sql code.  

%let startyr = 2008;
%let endyr = 2022;

%do tot_year=2008 %to 2022;

%let year=tot_year;

proc sql;

create table pop as select *

case when..... 

from "C:\POP_Data\bridge01_08a.sas7bdat"; ====> C:\POP_Data\b&year.sas7bdat"

[Quentin]

Sorry, I'm not understanding what you are asking.  Can you describe more of the big picture?

It looks like you have a directory full of SAS datasets named like :

bridge01_08a.sas7bdat

bridge01_09a.sas7bdat

bridge01_10a.sas7bdat

Is that right?

And what do you want to do with those datasets?  Do you want to rename the datasets?  Or are you trying to analyze the data in them?

Do all of these datasets have the same variables in them, so that it would be feasible to stack them into one dataset, with a variable for year?

If you forget about macro code, can you show the full code that would do what you want, for say just 3 datasets?  Once that is clear, we can help you think about how to automate it with macro code, or some other approach.

[stella7]

I didn't write all files.  FILES include 2008-2022 with same format.  Data size very big, so all file data add up together for one file is a good idea or not. I need an efficient way to same variables for each file (different year) to analyze data.

%let styear=2008; %let endyear=2022;

%do tot_year=&styear %to &endyear;

let year =tot_year

proc sql:

create table EX select as

 case
when age ge 10 and age le 14 then pop&year
else 0
end as AGES_10_14,
case
when age in (15,16,17) then pop&year
else 0
end as AGES_15_17

from "C:\POP_Data\&year.sas7bdat";

[Quentin · Accepted solution]

SAS is very good at analyzing big data.  If you keep the data in different files for each year, you will constantly need to analyze multiple files.

I would try putting them together into one big file.  When you do this, you can keep only the variables you need, and only the rows you need.

If you want to combine all SAS datasets in a folder that start with the letter B into one dataset, you could do it like:

libname bridge 'C:\POP_Data' ;
data bridges ;
  set bridge.b: indsname=_dsname ;
  length dsname $32 ;
  dsname=scan(_dsname,2,'.') ;
run ;

The variable DSNAME will show then name of the file which was the source for each record.  And you can use that to make a variable for year, or whatever logic.

[stella7]

Thanks . The code is simple and very easy to add up all dataset.

[Tom]

You need to explain the bigger picture better to allow us to suggest ways to get what you want.

It is very hard to get SAS macro code to generate the SAS code you want when you don't yet know what SAS code you want.

For example the snippet you just showed does not make much sense because you have a %DO loop that is regenerating the exact same dataset over and over.  

%do tot_year=&styear %to &endyear;
...
  create table EX  ...
%end;

At the end of the %DO loop that dataset EX will be what was made when TOT_YEAR had the value of ENDYEAR.  The dataset created for the other years will have been replaced.

[Tom]

It is not clear what you want to do here, but let's show some simple macro coding methods that might help with generating those types of strings from a YEAR value.

The main tricky thing is that the year part in your strings do not include the century, so you have strings with leading zeros.  One way to make sure one digit numbers get generated with leading zeros is to use the %SYSFUNC() to call the PUTN() function and apply the Z format to the number.

Example:

%do year = 8 to 22 ;
   %let twodigit = %sysfunc(putn(&year,z2.));
   %let oldname=bridge02_&twodigit;
   %let newname=b_20&twodigit;
....
%end;

Another way is to use the real year values (with the century).  Then you could use the %SUBSTR() macro function to pick off the last 2 digits.

%do year = 2008 to 2022 ;
   %let twodigit = %substr(&year,3);
   %let oldname=bridge02_&twodigit;
   %let newname=b_20&twodigit;
....
%end;

[stella7]

I tried to use this code and change values in column , "old".

This data file name. I want to change to b_2008.sas7bdat, b_2009.sas7bdat, b_2010.sas7bdat............b_2022.sas7bdat.

In your code, I put  :    old=%sysfunc(tranwrd(old, &oldname, &newname)); However, it doesn't work.

%do year = 2008 to 2022 ;
   %let twodigit = %substr(&year,3);
   %let oldname=bridge02_&twodigit;
   %let newname=b_20&twodigit;
    old=%sysfunc(tranwrd(old, &oldname, &newname));
%end;