BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rpg163
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

*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;

View solution in original post

12 REPLIES 12
Tom
Super User Tom
Super User

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
Super User Tom
Super User

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

I just get it.

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

art297
Opal | Level 21

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
Calcite | Level 5

THX, I will keep moving.

Tom
Super User Tom
Super User

*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
Calcite | Level 5

Thank you. I am learning your code.

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

Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4838 views
  • 7 likes
  • 4 in conversation