topic Re: generate new variable for multiple files using loop in SAS Programming

generate new variable for multiple files using loop

cphd — Mon, 13 Jan 2020 21:49:30 GMT

Hi all,

I am trying to generate a new variable in 20 different files. It seems that I can do it at once using a loop function. Would you please let me know how to do it?

I have a 20 sas data files: file1-file20

I simply want to generate one variable in each file: Age = 2019- Byear

data file1;
set file1;
age=2019-byear;
run;


* I am looking for a code to run the codes above at once for 20 files.;

Also, can anyone let me know how to change the format of each variables? I need to append the data (file1-file20) but it has an error due to different lengths... Is there anyway I can append them without losing any obs? Thank you.

Re: generate new variable for multiple files using loop

mkeintz — Mon, 13 Jan 2020 22:20:13 GMT

Do all your files (they are sas data sets, right?) have identical variables? If so, here is a non-macro-ized way to do it:

data newfile1 newfile2 ....   newfile20;
  set file1 (in=in1) file2 (in=in2) .... newfile20 (in=in20);
  age=2019-byear;
  if in1  then output newfile1;  else
  if in2  then output newfile2;  else
  ....
  if in20 then output newfile20;
run;

But that's a lot of typing, and requires that all the data sets have identical variables. Here's a way that takes less typing and accommodates different variable collections between the data sets. But it requires defining, and using, a macro:

%macro do_all_20;
  %do i=1 %to 20;
  data newfile&i;
    set file&i;
    age=2019-byear;
  run;
%mend;
%do_all_20;

The first program runs a single data step, inputting (and outputting) 20 datasets. The second runs 20 data steps, each with one input and output dataset.

Re: generate new variable for multiple files using loop

Patrick — Mon, 13 Jan 2020 22:21:51 GMT

Something like below should do the job.

To deal with the different lenghts: Use a LENGTH statement prior to the SET statement and there define the max length from any of the source tables.

data file1 file2 file3;
  set sashelp.class;
  testvar='1';
run;

data file4;
  set sashelp.class;
  testvar='22';
run;

data want;
  length testvar $2;
  set file1 - file4 indsname=_sourceFile;
  newar=2019-age;
  length sourceFile $32;
  sourceFile=_sourceFile;
run;

Re: generate new variable for multiple files using loop

Reeza — Mon, 13 Jan 2020 22:51:32 GMT

Is there a reason to keep these 20 files separate? Can you combine them and then process everything together, which seems like the most efficient solution. If not you could look into a macro (see tutorials below) or examples of loops in the second link.

This would run the code for all file data sets but the output would all be stored in a single output data set, called file_combined.

data file_combined;
set file1-file20 indsname=source;

dsn_source = source;
age = 2019-byear;

run;

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@cphd wrote:

Hi all,

I am trying to generate a new variable in 20 different files. It seems that I can do it at once using a loop function. Would you please let me know how to do it?

I have a 20 sas data files: file1-file20

I simply want to generate one variable in each file: Age = 2019- Byear
data file1;
set file1;
age=2019-byear;
run;


* I am looking for a code to run the codes above at once for 20 files.;
Also, can anyone let me know how to change the format of each variables? I need to append the data (file1-file20) but it has an error due to different lengths... Is there anyway I can append them without losing any obs? Thank you.

Re: generate new variable for multiple files using loop

cphd — Tue, 14 Jan 2020 15:59:13 GMT

Thanks for sharing the code. I used the macro function to do it but you forgot to add %end at the end. Here is the code that I used for the reference for future users.

%macro all20;
  %do i=1 %to 20;
  data newfile&i;
    set file&i;
    age=2019-byear;
  run;
%end
%mend all20;
%all20;