I'm new to SAS macro programming. I did a lot research on this topic, but I still can't figure out how to integrate those good examples into my problem here.
Problem:
I have 2 SAS tables to work with, table A and table B.
table A has a 6 month records. What I need to do is to extract distinct id weekly from this 6 months period.
Each iteration, I need to perform this procedure as shown below:
proc sql;
select distinct id
from table A
where date >= '27SEP2013'd and date <= '03OCT2013'd; /* first week of the 6 months period*/
quit;
proc sql;
select count(*) as CNT,
calculated CNT / (select count(distinct id) from table A) as response_rate
from table A as A, table B as B
where A.id = B.id
quit;
so I need to do this week by week for about 26 weeks to cover that 6 months period in Table A.
I don't know how to use the %Do start_date %To end_date %By(week) loop to do this iteration.
Any help? Thanks.
Why not change your first SQL to count distinct per week using a group by and WEEK() function?
You'll end up with something like the following for Table A
Week DistinctID
Can you post simple example data for A , B and the results.
Not sure why you can't join the two tables and use GROUP BY.
If you're new to SAS Macro programming then you're at risk to step into the trap to opt too fast for a macro based solution. Only use macro coding if you can't do it without. As Reeza and Tom already are hinting in your case it's highly likely that you don't need macro coding at all but simply need to get your SQL right. If you can provide some sample data and show us how the expected result should look like then I'm sure someone will come up with a way of how to get it done.
This sco wiki page has coded that explains the macro %do loop using date intervals.
Simply calculate week from your data and then use in group by:
data a;
set a;
by id date;
retain _dt;
if first.id then _dt=date;
wk=(date-dt+1)/7;
run;
proc sql;
select count(*) as CNT,
calculated CNT / (select count(distinct id) from table A) as response_rate
from table A as A, table B as B
where A.id = B.id
group by wk;
quit;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.