loop issue, with & without macro

[rpg163]

Hi,

     I try to do this in datastep, simply, update n 1 by 1 in the do loop,

%macro a;

     data a;

        set b;

        %let n=0;

        %do i=1 %to 12;

            %let n=%eval(&n+1);

                var&n=lag&n(var1);

        %end;

   run;

%mend a;

It is OK when I use marco, but when I try this in datastep, like follows, n seems never change. I don't understand why

data a;

        set b;

        %let n=0;

       do i=1 to 12;

            %let n=%eval(&n+1);

                var&n=lag&n(var1);

        end;

run;

Anyone can tell me why? Many thanks!

BTW, it seems so stupid to construct n first and than use n+1, cann't I just use i directly?

[Tom]

Macro is text generating tool.  Most macros generate text that looks like SAS code so that it can be passed to regular SAS for interpretation as as SAS statements.

Your first version generates these statements.

MPRINT(A):   data a;
MPRINT(A):   set b;
MPRINT(A):   var1=lag1(var1);
MPRINT(A):   var2=lag2(var1);
MPRINT(A):   var3=lag3(var1);
MPRINT(A):   var4=lag4(var1);
MPRINT(A):   var5=lag5(var1);
MPRINT(A):   var6=lag6(var1);
MPRINT(A):   var7=lag7(var1);
MPRINT(A):   var8=lag8(var1);
MPRINT(A):   var9=lag9(var1);
MPRINT(A):   var10=lag10(var1);
MPRINT(A):   var11=lag11(var1);
MPRINT(A):   var12=lag12(var1);
MPRINT(A):   run;

Your second version has no looping in the macro language and so will only generate one statement.

And since the macro progressing finishes before the generated data step runs your code is the equivalent of this:

%let n=0;
%let n=%eval(&n+1);
data a;
  set b;
  do i=1 to 12;
    var&n=lag&n(var1);
  end;
run;

Which is the same as:

data a;
  set b;
  do i=1 to 12;
    var1=lag1(var1);
  end;
run;

[Tom]

The reason you might have to resort to using macro logic to generate SAS code is that the LAG functions do not take the lag distance as an arguemet, but instead it is part of the actual function name.  That is why you cannot use the data step variable I to control the lag distance.

If you want to create 12 lagged variables and the number 12 is relatively constant it is probably easier to just use your editor to generate the 12 lines.

So what is your actual problem?  Perhaps there is a better way to handle it?

[rpg163]

Hi, Tom,

Thank you very much.

As the code is a little bit complex, so I want to make it simple. My actual question goes like this,

I am trying to convert character variables to numeric, these should be all numeric, but for some reason, I don't know, on import process, some variables turn to character, maybe because all values for these variables are missing, I think. As this is a excel file, I don't know how to import with type variables of the under control.

so I write this, BTW, under the help of  a friend here,

data zxc2_1;

    set zxc2;

        %let dsid=%sysfunc(open(zxczxc1));

        %let nvar=%eval(%sysfunc(attrn(&dsid,nvars))-1);

        %let close=%sysfunc(close(&dsid));

        array oldchar(*) _character_;

        array oldnumb(*) _numeric_;

        array COL(&nvar);

    do i=1 to dim(oldchar);

        COL(compress(vname(oldchar(i)),,'kd'))=input(oldchar(i),best12.);  

    end;

    do i=1 to dim(oldnumb);

        COL(compress(vname(oldnumb(i)),,'kd'))=oldnumb(i);

    end;

    rename COL2-COL&nvar=COL1-COL%eval(&nvar-1);

    keep COL2-COL&nvar;

run;

problem is, I have many such excel file, So I try to use marco,

but the dim now does not work, and I don't know why.

What should I deal with this?

THX!

[art297]

I'm confused!  This doesn't look at all like the question that started this thread.  However, that said, at least move your %let statements before the start of your datastep.  Otherwise, they don't take on the values until after the datastep has completed.

[rpg163]

I just get it.

Well, I now realized I am so ignorant on SAS.

[art297]

Don't feel like you are ignorant!  One of the things a lot of us like about SAS is the fact that the Institute keeps adding useful functionality each year.  The downside of that it that learning to use all of its capabilities is a never ending task and, each year, the learning curve for beginners gets a little steeper to climb.

[rpg163]

THX, I will keep moving.

[Tom · Accepted solution]

*RANT* The best solution is to not use Excel as a data entry tool.  It does not understand the concept of variables and treats every cell independently. *ENDRANT*

If you know what format you want the data have them give it to you in a CSV file and then read it with a data step where you define the variables.  You can use the import tool to generate the code for you and then modify it, but it is not very hard to write a data step to read a CSV file.

data want;

    length id 8 name $40 age 8 date 8;

    informat date yymmdd.;

    format date yymmdd.;

    infile 'myfile.csv' dsd truncover ;

    input id--date;

run;

Now to generate code to convert variable that are of the wrong type you can do that by quering the SAS metadata tables.  Look at DICTIONARY.COLUMNS (also available as the view SASHELP.VCOLUMN).  For example you could use PROC SQL to create macro variables that have the needed code.

proc sql noprint;
   select catx('=','_'||name,name)
        , cats('_',name,'=input(',name,',best.)')
        , name
      into :rename separated by ' '
         , :assign separated by ';'
         , :drop separated by ' '
      from dictionary.columns
      where libname='WORK'
        and memname='A'
        and type='char'
   ;
quit;
data b;
  set a ;
  &assign;
  rename &rename;
  drop &drop;
run;

[rpg163]

Thank you. I am learning your code.

But as I have many excel files, It is not a easy job to transfer...

[Cynthia_sas]

Hi:

  There is a HUGE difference between the macro %DO, inside a macro program and the DATA step DO, inside a "regular" DATA step program.

  When you write a macro program, basically, you are instructing the SAS Macro Facility how to do your typing for you. In the first example, the %DO is causing 12 statements to be typed for you...something like this:

   var1 = lag1(var1);

   var2 = lag2(var1);

   var3 = lag3(var1);

   var4 = lag4(var1);

   var5 = lag5(var1);

   var6 = lag6(var1);

   var7 = lag7(var1);

   var8 = lag8(var1);

   var9 = lag9(var1);

   var10 = lag10(var1);

   var11 = lag11(var1);

   var12 = lag12(var1);

When you tried the second example, in "open" code, you should have seen this message in the log:

ERROR: The %DO statement is not valid in open code.

You are correct that if the values of &i and &n are the same, then you would not need to create &n, you could use &i directly, within the context of a macro program (not in open code). In the regular DATA step context, if you want a simple iterative DO loop and you want to use the index variable I (as opposed to the macro variable &I), then you would need to use an ARRAY with I being used to provide the value for the array subscript.

As I said, the macro facility is generating code for you and then the generated code goes to the compiler. If there are no compile errors in the generated code, the code is then sent to be executed. By the time the code goes to the compiler, there are no %LET or %DO statements that the compiler sees. The %LET statement, for example, in "open" code is only resolved 1 time, when all the macro variables are resolved. In open code, the %LET will never change after the first time it is resolved.

Inside a macro program on the other hand, you are giving instructions for how to generate code statements. So, the %DO is generating statements repetitively. No data is being read by the macro program. Your input file is only read when the DATA step program executes, after all the % and & triggers have been resolved, and, after the resolved code has been compiled.

Reading more about macro programs and macro variable resolution will help you a lot in writing your program. It's not entirely clear to me what you want to do in your code. Does VAR1 exist in dataset B??? It looks to me like you will create VAR1 on the first iteration of the %DO loop.

I wonder whether you really need macro processing and/or whether you might actually need to create an ARRAY. Without knowing a bit more about what is in dataset B and what you want dataset A to look like it is hard to make constructive comments.

cynthia

[rpg163]

Cynthia,

Many Thanks, I just realize the huge difference between the macro %DO and datastep do. I do not know it before. THX.

My problem actually like this, as you may see, I try to put this into macro,

data zxc2_1;

    set zxc2;

        %let dsid=%sysfunc(open(zxczxc1));

        %let nvar=%eval(%sysfunc(attrn(&dsid,nvars))-1);

        %let close=%sysfunc(close(&dsid));

        array oldchar(*) _character_;

        array oldnumb(*) _numeric_;

        array COL(&nvar);

    do i=1 to dim(oldchar);

        COL(compress(vname(oldchar(i)),,'kd'))=input(oldchar(i),best12.);  

    end;

    do i=1 to dim(oldnumb);

        COL(compress(vname(oldnumb(i)),,'kd'))=oldnumb(i);

    end;

    rename COL2-COL&nvar=COL1-COL%eval(&nvar-1);

    keep COL2-COL&nvar;

run;

but the dim does not work, and I don't know why.

[Tom]

If you really have a series of Excel files to import do they all have the same variables?

And if your main variable type issue is caused by EMPTY columns then you should be able just identify and drop the missing columns.

Step one create a dataset with the preferred structure that you can use as a template (or even a master dataset).

Step two import the next file.

Step three identify the empty columns.  Look at this discussion for example: http://communities.sas.com/message/12474#12474

Step four convert to the standard structure with code like this:

data next_fixed;

* The next line will cause SAS to create the variables as defined in TEMPLATE but not actually include any data as 0 is always false;

  if 0 then set template;

   set next (drop=&missingvars);

run;