New to Macros! Need Help!

[Chutzler]

Hello,

I need to create a macro that adds a variable equal to one to a dataset, and then sum the counts of that variable and merges it to another dataset. I need to do this for datasets corresponding to the years 2004 through 2018. The structure of the data set names are "rp_equities_2004", "rp_equities_2005"...etc. I have attached code to this message with what I currently have. I know it is not correct but I was hoping someone could point me in the right direction. It's difficult because the files I am working with are massive, so simply running a check is difficult to do. Here is my current code:

%macro raven;
%do i = 2004 %to 2018;

data rp_equities_i; set rp_equities_i;
article = 1;
run;

proc sql;
create table media_cov_i as select distinct
a.*, b.count(article) as article_count_rel
from media_cov_(i-1) as a left join rp_equities_i as b
on a.rp_entity_id = b.rp_entity_id and a.datadate <= b.rpna_date_utc <= b.datadate_lead and b.relevence > 75;
quit;

%end

[PaigeMiller]

You set up a loop, with the macro variable i, but then you never reference this macro variable after that — you would reference it as &i

Then, your final %END needs a semi-colon after it. Also, you have to end a macro with %MEND; and you then need to call the macro

So with that in mind, here is my attempt to fix what you did (there may be other errors not described above)

%macro raven;
    %do i = 2004 %to 2018;
        data rp_equities_&i; 
            set rp_equities_&i;
            article = 1;
        run;
        proc sql;
             create table media_cov_&i as select distinct
             a.*, count(b.article) as article_count_rel
             from media_cov_%eval(&i-1) as a left join rp_equities_&i as b
             on a.rp_entity_id = b.rp_entity_id and a.datadate <= b.rpna_date_utc <= b.datadate_lead
             and b.relevence > 75;
        quit;
    %end;
%mend;
%raven

[Chutzler]

Thanks for the help! Quick question-- what does the %eval do?

[Tom]

https://www.google.com/search?q=%40sas.com+%25eval()

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n07pr39df9k7m3n1w3x1q09iewta.htm

[Reeza]

Why are you adding article=1, is that just for summing/counting?

[Reeza]

It's difficult because the files I am working with are massive, so simply running a check is difficult to do.

SAS has a nifty option - OBS= that allows you to set how many observations it processes and you can use that for testing. Set it to a smaller number for testing and when ready to run, set it to max. 

For example (CLASS has 19 records).

option obs=5;
proc means data=sashelp.class;
run;

option obs=max;
proc means data=sashelp.class;
run;

However, if you code using this style you cannot use this option as it will replace your original data set. This is one of the reasons this type of coding isn't recommended. 

data rp_equities_i; set rp_equities_i;

---

@Chutzler wrote:

Hello,

I need to create a macro that adds a variable equal to one to a dataset, and then sum the counts of that variable and merges it to another dataset. I need to do this for datasets corresponding to the years 2004 through 2018. The structure of the data set names are "rp_equities_2004", "rp_equities_2005"...etc. I have attached code to this message with what I currently have. I know it is not correct but I was hoping someone could point me in the right direction. It's difficult because the files I am working with are massive, so simply running a check is difficult to do. Here is my current code:

%macro raven;
%do i = 2004 %to 2018;

data rp_equities_i; set rp_equities_i;
article = 1;
run;

proc sql;
create table media_cov_i as select distinct
a.*, b.count(article) as article_count_rel
from media_cov_(i-1) as a left join rp_equities_i as b
on a.rp_entity_id = b.rp_entity_id and a.datadate <= b.rpna_date_utc <= b.datadate_lead and b.relevence > 75;
quit;

%end

---