Do Loop Question

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Do Loop Question


Hello Everyone,

I am trying to create a sas dataset by combining one file from every year since 2011.  For example, there are two files to combine so far: Completed_2011 and Completed_2012.  The macro variable &year has been set to 2012 in an earlier part of my program.  I am choosing the DO statement because i would like to automate this process so I wouldn't have to manually add a new file every year.  The error I get states "symbolic reference I not resolved".  Does anyone have any suggestions?  I really appreciate any input you may have!  Thank you Smiley Happy

(My Code)

data cobmined;

do i = 2011 to &year.;

     set completed_&i.;

end;

run;

(What it's supposed to do)

data combined;

     set completed_2011;

     set completed_2012;

run;

Thank you!


Accepted Solutions
Solution
‎11-26-2012 02:54 PM
Regular Contributor
Posts: 220

Re: Do Loop Question

You probably want to put everything in a macro so you can use &year (a macro variable) in a DO loop.  In order to use a macro variable in a DO loop, you have to use macro looping (i.e., %DO instead of just DO).  Also, you don't really want to put:

data combined;

     set completed_2011;

     set completed_2012;

run;

This will overwrite your first set statement.  You want it so look like this:

data combined;

     set completed_2011 completed_2012;

run;

To fix that problem, you want the SET statement outside of your loop, and you also have to close the statement with a ";", so you have to have the semi-colon outside of the loop as well.  Try the code below and see if you still get any errors.

%macro combine (year=);

      data combined;

            set

                  %do i=2011 %to &year.;

                        completed_&i.

                  %end;

            ;

      run;

%mend combine;

%combine (year=2012);

View solution in original post


All Replies
Solution
‎11-26-2012 02:54 PM
Regular Contributor
Posts: 220

Re: Do Loop Question

You probably want to put everything in a macro so you can use &year (a macro variable) in a DO loop.  In order to use a macro variable in a DO loop, you have to use macro looping (i.e., %DO instead of just DO).  Also, you don't really want to put:

data combined;

     set completed_2011;

     set completed_2012;

run;

This will overwrite your first set statement.  You want it so look like this:

data combined;

     set completed_2011 completed_2012;

run;

To fix that problem, you want the SET statement outside of your loop, and you also have to close the statement with a ";", so you have to have the semi-colon outside of the loop as well.  Try the code below and see if you still get any errors.

%macro combine (year=);

      data combined;

            set

                  %do i=2011 %to &year.;

                        completed_&i.

                  %end;

            ;

      run;

%mend combine;

%combine (year=2012);

PROC Star
Posts: 7,361

Re: Do Loop Question

Did you really want them to be set that way or simply concatenated to create on long file?  If it is the later, then you would include all of the file names within one set statement.  Regardless, I'd suggest something like the following rather than using a macro:

data completed_2011;

  input x;

  cards;

1

2

;

data completed_2012;

  input x;

  cards;

3

4

;

proc sql noprint;

  select memname into :files

    separated by " "

      from dictionary.tables

        where libname="WORK" and

              memname like "COMPLETED_%"

  ;

quit;

data want;

  set &files.;

run;

Regular Contributor
Posts: 173

Re: Do Loop Question

Thank you so much djbateman and Arthur!

I tried the code from both of you - both worked beautifully.  They are two different approaches but achived the same result.

Thank you very much !! Smiley Happy

Super User
Posts: 17,784

Re: Do Loop Question

You can also use shortcuts in sas SET statements if you have SAS 9.2 or higher

e.g.

data completed_2011;

  input x;

  cards;

1

2

;

data completed_2012;

  input x;

  cards;

3

4

;

data completed;

set completed:;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 288 views
  • 3 likes
  • 4 in conversation