topic Re: How can I use the do loop for a proc sql Iteration? in SAS Programming

How can I use the do loop for a proc sql Iteration?

Dozel — Mon, 24 Aug 2015 18:33:22 GMT

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.

Re: How can I use the do loop for a proc sql Iteration?

Reeza — Mon, 24 Aug 2015 19:05:01 GMT

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

Re: How can I use the do loop for a proc sql Iteration?

Tom — Mon, 24 Aug 2015 20:52:38 GMT

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.

Re: How can I use the do loop for a proc sql Iteration?

Patrick — Tue, 25 Aug 2015 09:56:07 GMT

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.

Re: How can I use the do loop for a proc sql Iteration?

Ron_MacroMaven — Tue, 25 Aug 2015 14:46:44 GMT

This sco wiki page has coded that explains the macro %do loop using date intervals.

http://www.sascommunity.org/wiki/Macro_Loops_with_Dates

Re: How can I use the do loop for a proc sql Iteration?

ndp — Tue, 25 Aug 2015 18:46:09 GMT

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;