topic Lag column variable in do loop in SAS Procedures

Lag column variable in do loop

ChristofferJ_Weissert — Tue, 29 Jul 2014 14:00:47 GMT

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

Re: Lag column variable in do loop

RW9 — Tue, 29 Jul 2014 14:11:03 GMT

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.

Re: Lag column variable in do loop

ChristofferJ_Weissert — Tue, 29 Jul 2014 14:26:41 GMT

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.

Re: Lag column variable in do loop

RW9 — Tue, 29 Jul 2014 14:32:48 GMT

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;

Re: Lag column variable in do loop

ChristofferJ_Weissert — Tue, 29 Jul 2014 14:38:46 GMT

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

Kind regards

Christoffer

Re: Lag column variable in do loop

haikuobian — Tue, 29 Jul 2014 14:39:53 GMT

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

Re: Lag column variable in do loop

Ksharp — Tue, 29 Jul 2014 14:40:15 GMT

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

Re: Lag column variable in do loop

ChristofferJ_Weissert — Tue, 29 Jul 2014 14:56:15 GMT

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?

Re: Lag column variable in do loop

Ksharp — Wed, 30 Jul 2014 13:04:28 GMT

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

%eval(&year + 4)