BookmarkSubscribeRSS Feed
Dozel
Calcite | Level 5

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.

5 REPLIES 5
Reeza
Super User

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

Tom
Super User Tom
Super User

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.

Patrick
Opal | Level 21

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.

Ron_MacroMaven
Lapis Lazuli | Level 10

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

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

ndp
Quartz | Level 8 ndp
Quartz | Level 8


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;

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 2209 views
  • 6 likes
  • 6 in conversation