topic Re: Do loop over multiple tables in SAS Programming

Do loop over multiple tables

Kjeldsen89 — Thu, 30 Jul 2020 10:07:44 GMT

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.

Re: Do loop over multiple tables

Kurt_Bremser — Thu, 30 Jul 2020 10:31:20 GMT

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

Re: Do loop over multiple tables

Kjeldsen89 — Thu, 30 Jul 2020 11:09:12 GMT

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

Re: Do loop over multiple tables

Kurt_Bremser — Thu, 30 Jul 2020 11:15:30 GMT

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;

Re: Do loop over multiple tables

Kjeldsen89 — Thu, 30 Jul 2020 11:23:57 GMT

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

Re: Do loop over multiple tables

Kurt_Bremser — Thu, 30 Jul 2020 11:31:23 GMT

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)

Re: Do loop over multiple tables

Kjeldsen89 — Thu, 30 Jul 2020 11:45:54 GMT

PERFECT!