Help using Base SAS procedures

Lag column variable in do loop

Reply
New Contributor
Posts: 4

Lag column variable in do loop

Hi

I've just started using macros in SAS so I apologize if my question is too simple..

I have a dataset containing something like this:

id     kom1995     kom1996     kom1997     kom1998     kom1999     kom2000     kom2001     kom2002     ...     kom2013

1     45               45               45               45               45               48               48               48              ...     48

2     ...                                                                      ...                                                                       ...     48

.

.

.

n     47               47               47               47               47               47               47               47               ...     47

which really isn't the core of my question, but anyway nice to have in mind.

My big dataset above is called kommune_s.

So, I have a timeseries spanning 18 years. I want 17 smaller datasets containing:

Name of dataset 1: kom1995-1996

id     kom1995     kom1996    

1     45               45             

2     ...                                

.

.

.

n     47               47            

Name of dataset 2: kom1996-1997

id     kom1996     kom1997    

1     45               45             

2     ...                                

.

.

.

n     47               47            

Name of dataset 17: kom2012-2013:


id     kom2012     kom2013    

1     45               45             

2     ...                                

.

.

.

n     47               47            

OK, sorry for the long text.

What I thought that I was able to do was to have SAS make these 17 datasets using a macro and the above dataset kommune_s

Something like this:

%macro year;

%do year=1996 %to 2013;

data komlag(&year.)to&year.;

set kommune_s;

keep id komlag(&year.) kom&year.;

run;

%end; %mend; %year;

Obviously I'm doing something wrong, but I haven't quite figured the macro-feature out yet.

I really hope that there is someone who understands my question and is able to help me.

Kind regards

Christoffer

Super User
Super User
Posts: 7,955

Re: Lag column variable in do loop

Posted in reply to ChristofferJ_Weissert

Well you can have:

data _null_;

     do I=1995 to 2006;

          call execute('data KOM'||strip(put(I,best.))||'_'||strip(put(I+1,best.))||';

                                   set commune_s;

                                   where '||strip(put(I,best.))||' <= year <= '||strip(put(I,best.))||';

                              run;');

     end;

run;

However I would suggest that you should remap your original dataset to a normalized table rather than transposed:

ID          YEAR          RESULT

1            1996            47

1            1997             56

1            1998               34

...

The above will be a lot easier to work with than a large dataset with many variables, or lots of smaller datasets with partial info in them.

New Contributor
Posts: 4

Re: Lag column variable in do loop

Thank you for your answer

The reason why I haven't transposed my dataset is that I have +100000000 observations which is already a mess. Furthermore, I'm interested in following that one ID year from year ie. commune-shifts.

I'm not sure if I understood your answer exactly. Does it have to be like the above? I tried something like:

%macro year;

%do year=1996 %to 2013;

data s&year.;

set kommune_s;

keep id komlag&year. kom&year.;

run;

%end; %mend; %year;

I'm pretty sure that this is the critical thing:

%macro year;

%do year=1996 %to 2013;

data s&year.;

set kommune_s;

keep id komlag&year. kom&year.;

run;

%end; %mend; %year;

but that is essentially what I want to do. It is quite simple to do manually but I was just wondering if there was an easier way do to this for future use.

Manually I would just do:

data s1995to1996;

set kommune_s;

keep id kom1995 kom1996;

run;

data s1996to1997;

set kommune_s;

keep id kom1996 kom1997;

run;

and so forth.

Super User
Super User
Posts: 7,955

Re: Lag column variable in do loop

Posted in reply to ChristofferJ_Weissert

Yes, you can do a macro, however you can't use lag like that as that's a datastep function so;

data kommune_s;

  attrib id kom1995 kom1996 kom1997 format=best. ;

  id=1; kom1995=6; kom1996=4; kom1997=3; output;

run;

%macro year;

     %do year=1996 %to 1997;  

          data s%trim(&year.);   /* I would include trim as otherwise you will get spaces preceding */

               set kommune_s;

               keep id kom%eval(&year.-1) kom%trim(&year.);

          run;

      %end;

%mend;

%year;

New Contributor
Posts: 4

Re: Lag column variable in do loop

Ah, I see. Thank you very, very much.

Kind regards

Christoffer

Occasional Contributor
Posts: 17

Re: Lag column variable in do loop

Posted in reply to ChristofferJ_Weissert

Could it be as simple as the following:

%MACRO YR;

  %DO YEAR=1996 %TO 2012;

  DATA KOM&YEAR.TO%EVAL(&YEAR+1);

SET kommune_s(KEEP=ID A&YEAR A%EVAL(&YEAR+1));

RUN;

%END;

%MEND;

%yr;

Please note: given the size of your data, you want to 'keep' the variables when reading in, not when output.

Haikuo

Super User
Posts: 10,028

Re: Lag column variable in do loop

Posted in reply to ChristofferJ_Weissert

Your code looks good .

%macro year;

%do year=1996 %to 2013;

data komlag&year.to%eval(&year + 1);

set kommune_s;

keep id komlag&year    kom%eval(&year + 1);

run;

%end; %mend;

%year

New Contributor
Posts: 4

Re: Lag column variable in do loop

It works perfect now, thanks! I think that the problem was that I didn't knew the %eval statement. Actually, right now I'm just using it without exactly knowing what i does. Is it enabling me to change my variable names with proportional loops such as +1 or +4 if needed?

Super User
Posts: 10,028

Re: Lag column variable in do loop

Posted in reply to ChristofferJ_Weissert

Yes. %eval() make macro variable +-*/  as it does in data step .

%eval(&year + 4)

Ask a Question
Discussion stats
  • 8 replies
  • 290 views
  • 0 likes
  • 4 in conversation