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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.