Macro in a datastep

[CathyVI]

Hello,

Macro is a new area for me and am trying to understand how it works. I have a data that runs many years (2006-2020). I have written a code for year 2006 but i will like to repeat the code for all the years without written the same code repeatedly. I believe this is one of the function of macro so I will appreciate any help. Thanks

data out_2006 (where=(m=1 or b=1 or mb=1 )
keep= pt_id clm b m mb);
set a.out_2006;
if CD in ('55700') then B=1; else B=0;
if CD =  '77021' then MB=1; else MB=0;
if CD in ('B43', 'B53', 'BW3') then M=1; else M=0;
run;

[PaigeMiller]

So this is very good, your first step is what we always advise (but which most people don't do) — create working code without macros first!! Congratulations! 💯

Now that you have working code without macros, what would need to change to make this code run for 2007 or 2008 or ... ? Just the two lines where you have currently hard-coded 2006, right? Plus you would need a loop over the years.

So a macro that loops through all years would be something like this, where the only changes are a macro loop and replacing the 2006 with a macro variable determined by the loop:

%macro around(start_year=,end_year=);
    %do year=&start_year %to &end_year;
        data out_&year. (where=(m=1 or b=1 or mb=1)
            keep= pt_id clm b m mb);
            set a.out_&year.;
            if CD in ('55700') then B=1; else B=0;
            if CD =  '77021' then MB=1; else MB=0;
            if CD in ('B43', 'B53', 'BW3') then M=1; else M=0;
        run;
    %end;
%mend;
%around(start_year=2006,end_year=2020)

[Cynthia_sas]

Hi:
To help you understand about macro variables and macro programs, here's an "oldie but goodie" paper on Macro Basics that may help you. https://support.sas.com/resources/papers/proceedings13/120-2013.pdf
Cynthia

[CathyVI]

@PaigeMiller @Cynthia_sas @ballardw 

Thank you so much for the codes and the resources. The code works perfectly.
Do you think macro will work for a merge file? Most importantly since I have to proc sort the two dataset before merging. I have a code example below

proc sort data=out_2006; 
by id; 
run;
proc sort data=out_2007; 
by id; 
run;

Data out_06_07;
merge out_2006 (in=a) out_2007 (in=b);
by pt_id clm b m mb ;
if b or a;
run;

[Tom]

If you do want to use macro code it will be easier to replicate the pattern if the year values used the same number of digits everywhere.

data out_2006_2007;
  merge out_2006 (in=a) out_2007 (in=b);

I do not see how this code pattern would extend to more than 2 years.  Would you want something like:

data out_2006_2009;
  merge out_2006 (in=a) out_2007 (in=b) out_2008(in=c) out_2009(in=d);

In that case I would recommend using the same naming pattern for the names for the IN= indicator variables.  

data out_2006_2009;
  merge out_2006 (in=in2006) out_2007 (in=in2007) out_2008(in=in2008) out_2009(in=in2009);

Now it will be easier for the macro to generate that pattern.

But I really wonder about what these dataset contain that would make sure a merge meaningful.  Do the dataset really have completed distinct sets of VARIABLES (other than the key variables) because if not the those variables will have only the values contributed by the last dataset.

Perhaps instead you meant to STACK or INTERLEAVE the datasets instead of MERGing them?

data out_2006_2009;
  set out_2006-out_2009;
  by pt_id clm b m mb ;
run;

[ballardw]

---

@CathyVI wrote:

@PaigeMiller @Cynthia_sas @ballardw 

Thank you so much for the codes and the resources. The code works perfectly.
Do you think macro will work for a merge file? Most importantly since I have to proc sort the two dataset before merging. I have a code example below

proc sort data=out_2006; 
by id; 
run;
proc sort data=out_2007; 
by id; 
run;

Data out_06_07;
merge out_2006 (in=a) out_2007 (in=b);
by pt_id clm b m mb ;
if b or a;
run;

---

Is this only for two years, always exactly two years? Or does this need to extend to merging 3, 10 or 20 at one time?

Note that the data set list mentioned early does also work with MERGE.

Note that your "If b or a;" is the default behavior for a merge: output data if either data set contributes an observation. So it may be that you don't need the (in = ) bits or that if at all.

[Kurt_Bremser]

This won't work if the datasets share the same structure (identical variable names throughout). Values would be overwritten in hard to predict ways.

And your BY statements in the SORT procedures and the DATA step don't match, so you will have ERRORs.

[PaigeMiller]

---

@CathyVI wrote:

@PaigeMiller @Cynthia_sas @ballardw 

Thank you so much for the codes and the resources. The code works perfectly.
Do you think macro will work for a merge file? Most importantly since I have to proc sort the two dataset before merging. I have a code example below

proc sort data=out_2006; 
by id; 
run;
proc sort data=out_2007; 
by id; 
run;

Data out_06_07;
merge out_2006 (in=a) out_2007 (in=b);
by pt_id clm b m mb ;
if b or a;
run;

---

Hello @CathyVI remember the advice I stated in my first post in this thread? I said you need to create working SAS code without macros. I don't think (and others don't think) this is working code, in the sense that it does not do what you want. Could you make sure that it works and accomplishes the desired task by looking at the output data set named OUT_06_07 to see if it contains the desired information.

[ballardw]

---

@CathyVI wrote:

Hello,

Macro is a new area for me and am trying to understand how it works. I have a data that runs many years (2006-2020). I have written a code for year 2006 but i will like to repeat the code for all the years without written the same code repeatedly. I believe this is one of the function of macro so I will appreciate any help. Thanks

data out_2006 (where=(m=1 or b=1 or mb=1 )
keep= pt_id clm b m mb);
set a.out_2006;
if CD in ('55700') then B=1; else B=0;
if CD =  '77021' then MB=1; else MB=0;
if CD in ('B43', 'B53', 'BW3') then M=1; else M=0;
run;

---

Perhaps instead of creating a separate output set for each input create one with all the data:

data out_Combined (where=(m=1 or b=1 or mb=1 )
keep= pt_id clm b m mb);
set a.out_2006 - a.out_2020;
if CD in ('55700') then B=1; else B=0;
if CD =  '77021' then MB=1; else MB=0;
if CD in ('B43', 'B53', 'BW3') then M=1; else M=0;
run;

The set statement will use lists such as the above to read sequentially numbered data sets.

You might use the INDSNAME option to create a variable that held the name of the contributing source set for each record if needed.

Note that when creating 1/0 coded variables in SAS you can use

B= ( CD in ('55700') );
MB = (CD =  '77021') ;
M = ( CD in ('B43', 'B53', 'BW3') );

SAS provides the result of logical expressions as numeric 1 for true and 0 for false.

I use () to make sure which part is the logical expression is clear.