How to cycle through a list of numbers in Proc SQL code

[CatM]

Hi Everyone, 

i need to figure out how to loop through a list of numbers using a macro/do loop. I have tried the same approach as a list of variables but that doesn't seem to work at all. I need to run the code for a set of clinic numbers but don't want to have to change the clinic number and run it a million times if it can be avoided. 

thanks!

cat 

%macro doit(list);
%let n=%sysfunc(countw(&list));
%do i=1 %to &n;
%let val = %scan(&list,&i);
%mend;
%doit(124 70 76 57 79 221);
proc sql;

CONNECT to odbc(dsn=dbwh);
CREATE TABLE bftest as SELECT * from connection to odbc
(
SELECT P.FileDate, CLINICID,
SUM( Mo0Count) as Mo0Count1, Mo0CountTotal, R.PACKET,
CASE WHEN convert(float, Mo0CountTotal) > 0
THEN SUM(Mo0Count) / convert(float, Mo0CountTotal)
ELSE 0 END as Mo0FEEDPercent
FROM dbo.BreastfeedingEthnicity as P
JOIN dbo.Dates D
ON p.FileDate = D.DateText
JOIN (SELECT *,
CASE WHEN PGLABEL = 'IB -BF Fully' THEN 'FullyBF'
WHEN PGLABEL = 'IF -Formula' THEN 'FORMULA'
WHEN PGLABEL IN ('IC -BF Mostly', 'IO -BF Some') THEN 'MOSTSOME'
ELSE '' END AS PACKET
FROM dbo.PacketGroupReference) as R
ON P.PacketGroup = R.PacketGroup
JOIN ( SELECT FileDate, SUM(Mo0Count) as Mo0CountTotal
FROM dbo.BreastfeedingEthnicity as P
WHERE FileDate >= '2017-10'
AND AgencyID = 314 AND ClinicID = %doit
GROUP BY CLINICID, FILEDATE ) as T
ON P.FileDate = T.FileDate

WHERE P.FileDate >= '2017-10'
AND AGENCYID =314
AND CLINICID = %doit
GROUP BY CLINICID, P.FileDate, Mo0CountTotal, PACKET
ORDER BY CLINICID, P.FILEDATE
);
%end;
quit;

[Krueger]

I feel like you have over complicated this, are you wanting to do a MACRO or use SQL to accomplish this? Use of both seems redundant and over complicating it.

If your going to use a MACRO then just plug in your DOIT list. 

If your going to use SQL Pass Through then just do a simple WHILE loop:

DECLARE @i int = 0

WHILE @i < 20
BEGIN
    SET @i = @i + 1
    /* do some work */
END

Performance wise CTE's may be the way to go for this however. https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sq...

[CatM]

so what should it look like if i choose to go with the CTE code for the list of clinics i have 

[mkeintz]

You are using the macro %doit to present a sequence of values that you merely compare to CLINICID.  So you are producing this expression:

CLINICID=124 70 76 57 79 221;

Instead you probably want

CLIICID in (124,70,76,57,79,221)

So I suggest you don't make a macor DOIT, but instead make a macro variable (call is CSVLIST), as in:

%let csvlist=124,70,76,57,79,221;

at the beginning/

Then, inside the PROC SQL you could use

clinicid in (&csvlist)

In other words, get data for all clinics of interest at once in data set BFTEST, sorted by CLINICID (you already have ORDER BY CLINICID).

Then later, to analyze each clininc in BFTEST (say it is a proc reg for each clinicid), you can:

proc reg data=bftest ….. ;
  by clinicid ;
  model …;
quit;

which does the analysis for each level of clinicid that you put in bftest.  I've put ellipses where you need to fill out the analysis program.

[CatM]

I had tried this orginally but because of the sum command i have in the SQL this would cause all my totals to add up and i need the totals by clinic. 

[Tom]

---

@CatM wrote:

I had tried this orginally but because of the sum command i have in the SQL this would cause all my totals to add up and i need the totals by clinic. 

---

If you want to use the aggregate SUM() function in SQL to get the sum for groups then use the GROUP BY clause in the query.

Something like this:

select id,sum(x) as sum_x
from have
group by id
where id in (&idlist)
;

[CatM]

yes i have the code formatted with the group by and where statements for the tables. for some reason it is still adding up multiple totals and different counts for the same clinic and time point 

[Tom · Accepted solution]

Please post code using the Insert Code or Insert SAS Code button.

The code you posted before appears to be summing the same variable in two different places, without using an alias to indicate what actual value of that variable you are summing.  Which version is not getting the result you want?

It looks like you are actually running that SQL code in some remote database that you connect to with ODBC.  So perhaps you need to query on a forum for that database about any issues that system has with performing aggregate functions?

[CatM]

specifying the specific count i needed to print out from the second piece of code where i asked for the sum worked! thanks!

[mkeintz]

You could do a sum function with group by clinicid+ other variables in a select clause, then INNER JOIN that selection with the original data on some unique match where that "outer" selection does a sum associated with group by clinicid only.

Example using SASHELP.CLASS, retrieving weight for individual, then summing weight for each SEX*AGE group, then also summing for each SEX:

proc sql;
  select a.name,a.weight,a.sex,a.age,sumw_by_sex_age,sum(weight) as sumw_by_sex
  from sashelp.class as a
  inner join
    (select sum(b.weight) as sumw_by_sex_age 
     from sashelp.class  as b
     group by b.sex,b.age)
  on a.name=b.name
  group by a.sex
  order by a.sex,a.age
  ;
quit;

I have no idea of how efficient this might be.  But it can be done.