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
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.
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.
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;
Ah, I see. Thank you very, very much.
Kind regards
Christoffer
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
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
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?
Yes. %eval() make macro variable +-*/ as it does in data step .
%eval(&year + 4)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.