BookmarkSubscribeRSS Feed
ChristofferJ_Weissert
Calcite | Level 5

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ChristofferJ_Weissert
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

ChristofferJ_Weissert
Calcite | Level 5

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

Kind regards

Christoffer

haikuobian
Fluorite | Level 6

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

Ksharp
Super User

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

ChristofferJ_Weissert
Calcite | Level 5

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?

Ksharp
Super User

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

%eval(&year + 4)

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!

What is Bayesian Analysis?

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.

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
  • 8 replies
  • 929 views
  • 0 likes
  • 4 in conversation