Do loop over multiple tables

[Kjeldsen89]

Hello

I'm quite new to the SAS platform.

I'm trying to make a data extract from some tables with identical names, but with different years as prefix, ex

UDDA2002

UDDA2003

UDDA2004
UDDA2005

UDDA2006

etc.

I have to extract the exact same variables from the different tables, but instead of doing it manually I was wondering if I could do this in a do-loop instead?

lets say I want to extract the variables pnr, x and y from the tables and save them as UDDA2004, UDDA2005 etc. on a different location than my import location.

Kind regards Frank.

[Kurt_Bremser]

First, create a string with dataset names that you can use in a SET Statement:

data _null_;
length string $32767;
do year = 2002 to 2006;
  string = catx(' ',string,cats('UDDA',year));
end;
call symputx('datasets',string,'g');
run;

data want;
set &datasets.;
keep pnr x y;
run;

Also see the following documentation:

DO Statement: Iterative 

CATS Function

CATX Function

CALL SYMPUTX Routine 

KEEP Statement

[Kjeldsen89]

Thank you Kurt!

The first part of the code works fine. The last part of the code doesn't work for me. Not quite sure if i'm understanding it correct, but I want to generate 5 new datasets with only pnr, x and y saved as UDDA2002, UDDA2003, UDDA2004, UDDA2005 and UDDA2006 on a new location.

I have two libnames:

origin and rawdata. Origin is where I gets the data from and rawdata is where I want to locate the new extracted UDDA data of pnr, x and y.

I have tried this:

data rawdata.want

set origin.&datasets.;

keep pnr x y;

run;

Then the log tells me that work.UDDA2002 etc. does not exist.

Kind regards Frank

[Kurt_Bremser · Accepted solution]

The fact that you have those datasets in libraries outside of WORK is crucial information and should have been included in your question.

Since each entry in the SET statement needs to completely describe (including the libname) the dataset in question, the library needs to be included when the string is built:

data _null_;
length string $32767;
do year = 2002 to 2006;
  string = catx(' ',string,cats('origin.UDDA',year));
end;
call symputx('datasets',string,'g');
run;

[Kjeldsen89]

My fault! I will remember that next time.

Thank you for your help.

It seemes like it have appended the UDDA2002-UDDA2006 instead of saving them as 6 new seperate datasets. Is there any way I can fix this in the data step? maybe use the string &datasets l just defined?

Kind regards Frank

[Kurt_Bremser]

That's where I misread your question (better: not read your question completely), my bad.

So we need to run the same data step repeatedly, so we will use a macro:

%macro extract(start,end);
%do year = &start. %to &end.;

data rawdata.udda&year.;
set origin.udda&year. (keep=pnr x y);
run;

%end;
%mend;
%extract(2002,2006)

[Kjeldsen89]

PERFECT!