Do Loop

[NazaninSAS]

Hi,

why my code does not go to the loop?

basically, the code should give me 6 reports (one for each region), but it gives me only one report which is total of all regions.

proc sql;

do i=1 to 6;

select

case i

when i=1 then REGION="HQ"

when i=2 then REGION="ATLANTIC"

when i=3 then REGION="PACIFIC"

when i=4 then REGION="PRAIRIES"

when i=5 then REGION="ONTARIO"

when i=6 then REGION="QUEBEC"

end as i; end as REGION;

select Band2 format=$8., count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

group by Band2

union all

select 'TOTAL' , count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

;

end;

i=i+1;

Quit;

Thanks,

Nazanin

[PeterClemmensen]

Did you write this code yourself or did someone give it to you?

Just to be clear, you want six distinct reports, correct?

[NazaninSAS]

wrote it myself.

this is the original one:

proc sql;

title 'Self Identification by Age Group, HQ Region';

select Band2, count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="HQ"

title 'Self Identification by Age Group, HQ Region';

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="HQ"

;

TITLE;

Quit;

proc sql;

title 'Self Identification by Age Group, Atlantic Region';

select Band2, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="ATLANTIC"

title 'Self Identification by Age Group, Atlantic Region';

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="ATLANTIC"

;

TITLE;

Quit;

proc sql;

title 'Self Identification by Age Group, PACIFIC Region';

select Band2, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="PACIFIC"

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="PACIFIC"

;

TITLE;

Quit;

proc sql;

title 'Self Identification by Age Group, ONTARIO Region';

select Band2, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="ONTARIO"

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="ONTARIO"

;

TITLE;

Quit;

proc sql;

title 'Self Identification by Age Group, PRAIRIES Region';

select Band2, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="PRAIRIES"

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="PRAIRIES"

;

TITLE;

Quit;

proc sql;

title 'Self Identification by Age Group, QUEBEC Region';

select Band2, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="QUEBEC"

group by Band2

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION="QUEBEC"

;

TITLE;

Quit;

but I want o combine all together to make it shorter and more efficient.

Thanks,

Nazanin

[PeterClemmensen]

While I am not fund of this approach, here is something that builds on your own code that you are familiar with. It does the job of making the code shorter, while it is still the exact same code that will run. 

I think PROC REPORT is the right tool to use here, but do something like this if you want to keeo your PROC SQL approach 🙂

data callstack;
   length string $1000;
   do region='HQ', 'ATLANTIC', 'PACIFIC', 'PRAIRIES', 'ONTARIO', 'QUEBEC';
      string=compbl(cats(
         "
         proc sql;
         title 'Self Identification by Age Group, ", region, " -Region';
         select Band2, count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,
         calculated SI_PRI/calculated PRI 'Return rate' format=percent8.1
         from work.Si
         where REGION=", region, ";
         title 'Self Identification by Age Group, ", region, " -Region';
         group by Band2
         union all
         select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,
         calculated SI_PRI/calculated PRI 'Return rate' format=percent8.1
         from work.Si
         where REGION=", region, ";
         ;
         TITLE;
         Quit;
         "
         ));
      output;
      call execute(string);
   end;
run;

[PeterClemmensen]

Note, the above code is untested, as I do not have any data to work with.

[NazaninSAS]

unfortunately no!

it changed my input data!

[PeterClemmensen]

If you by input data mean the data set work.si, then no. I promise you that my code did nothing to your input data 🙂

Please post the log if it generated any errors.

[NazaninSAS]

Thanks,

I tried proc tabulate, no luck! and also it was not the good tool. some SAS folks also recommended PROC REPORT. I have to review it!

I was struggling with PROC report as well! do you know the equivalent to PROC report on Enterprise. sometimes, when I use Enterprise, I can figure out the codes foe SAS base.

best regards,

Nazanin

[PeterClemmensen]

I'm not sure what you mean by "the equivalent to PROC report on Enterprise"?

But yes, PROC REPORT is pretty neat. I recommend that you start by reading some of the Examples from the PROC REPORT SAS Documentation. They will get you far. 

Did my code answer answer work for you?

[ballardw]

---

@NazaninSAS wrote:

Thanks,

I tried proc tabulate, no luck! and also it was not the good tool. some SAS folks also recommended PROC REPORT. I have to review it!

I was struggling with PROC report as well! do you know the equivalent to PROC report on Enterprise. sometimes, when I use Enterprise, I can figure out the codes foe SAS base.

best regards,

Nazanin

---

Large economy sized hint: Provide some example starting data we can use.

At this point we do not know anything about the structure of your data, which role any of the named variables except Region might play exactly , and since you are using count aggregates we can't even tell if the other variables are character or numeric.

When you started asking questions on the forum there were some hints about asking good questions and one of them is providing example data.

Any summary / reporting procedure requires knowledge of the data set structure. A statement like "I tried proc tabulate, no luck! " does not describe what went wrong, you don't show the actual code you attempted for tabulate, any log messages if there were errors or data details.

And SQL gets very cumbersome for multiple summaries based on differing groups of variables so I tend to use Proc Summary to create such sets. Perhaps something like this (which fails if PRI or SI_PRI are character, which we don't even know yet).

proc summary data=work.final;
   class region;
   var si_pri pri;
   output out=work.summary (drop=_freq_) n = ;
run;
data want;
   set work.summary;
   if _type_=0 then Region='Total';
   ReturnRate = si_pri/pri;
   select (region);
      when ("HQ")           order=1;  
      when ("ATLANTIC")     order=2;
      when ("PACIFIC")      order=3;
      when ("PRAIRIES")     order=4;
      when ("ONTARIO")      order=5;
      when ("QUEBEC")       order=6;
      when ("Total")        order=7;
      otherwise;
   end;
   label 
      Region     = "Region"
      ReturnRate = "Return Rate"
      si_pri     = "Completed surveys"
      pri        = "Employees"
   ;
      
run;
proc sort data=want;
   by order;
run;

proc print data=want noobs label;
   var region si_pri pri returnrate;
   format di_pri pri comma9. returnrate percent8.1;
run;

[r_behata]

Have you considered using Macros. Code is untested.

%let Region_=HQ ATLANTIC PACIFIC PRAIRIES ONTARIO QUEBEC;

%let i=1;
%let Region=%scan(&Region_,&i);

%macro gen_report;

%do %until(&Region eq %str());
	proc sql;
		title "Self Identification by Age Group, &Region Region";
		select Band2, count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

			calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

		from work.Si

		where REGION="&Region"

			title 'Self Identification by Age Group, &Region Region';
		group by Band2

			union all

		select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

			calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

		from work.Si

		where REGION="&Region"

		;
		TITLE;
	Quit;

	%let i=%eval(&i+1);
%let Region=%scan(&Region_,&i);

%end;
%mend;

%gen_report

[ballardw]

---

@NazaninSAS wrote:

Hi,

why my code does not go to the loop?

basically, the code should give me 6 reports (one for each region), but it gives me only one report which is total of all regions.

proc sql;

do i=1 to 6;

select

case i

when i=1 then REGION="HQ"

when i=2 then REGION="ATLANTIC"

when i=3 then REGION="PACIFIC"

when i=4 then REGION="PRAIRIES"

when i=5 then REGION="ONTARIO"

when i=6 then REGION="QUEBEC"

end as i; end as REGION;

select Band2 format=$8., count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

group by Band2

union all

select 'TOTAL' , count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

;

end;

i=i+1;

Quit;

Thanks,

Nazanin

---

Did you read the log?

I expect there should have been an error message similar to:

86   do i= 1 to 6;
     --
     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

because DO loops are not supported by Proc SQL. that explains why the loop doesn't work.

[Kurt_Bremser]

do/end are data step statements, they don't work in proc sql. Studying the log (Maxim 2) will alert you to this.

If you want to repeat code, use a %do/%end macro loop.

[Reeza]

As others have indicated DO loops do not work in PROC SQL.

To repeat a particular set of code N times you have a few options but a macro is usually one of the faster ways. Another is to use BY groups. 

Here's an explanation of how to turn your program into a macro step by step. If you follow these steps you're less likely to run into any errors or issues.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

But as others have indicated you should do this differently. I would recommend doing it in one step personally:

proc sql;
create table want as

select Band2, Region, 1 as sort_order, count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

where REGION in ('HQ', 'ATLANTIC', 'PACIFIC', "PRAIRIES')


group by Band2 , region

union all

select region, 99 as sort_order,  'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si, region

where region in ('HQ', 'ATLANTIC', 'PACIFIC', "PRAIRIES')

;



Quit;

And as others have indicated, using PROC SQL for summary calculations is fine, but PROC REPORT and TABULATE create better reports. 

You seem new at SAS, so I would recommend taking the free e-courses and checking out the how-to tutorials on video.sas.com

If you need help with a specific topic, such as PROC REPORT, try searching on Lexjansen.com. It has a ton of resources. 

Your questions also appear a bit scattered. I suspect you know how to program in another language and are trying to bring that into SAS. The guidelines below can help frame the questions better so you get solutions faster that work for you. In general, show what you have, what you want, and what you tried, preferably in that order as well. And last but not least, explain what you're trying to do, don't just put the code out there. If your code is wrong but we don't know what you're trying to do we can't really help you besides say that's not valid. Help us help you. 

https://stackoverflow.com/help/how-to-ask

---

@NazaninSAS wrote:

Hi,

why my code does not go to the loop?

basically, the code should give me 6 reports (one for each region), but it gives me only one report which is total of all regions.

proc sql;

do i=1 to 6;

select

case i

when i=1 then REGION="HQ"

when i=2 then REGION="ATLANTIC"

when i=3 then REGION="PACIFIC"

when i=4 then REGION="PRAIRIES"

when i=5 then REGION="ONTARIO"

when i=6 then REGION="QUEBEC"

end as i; end as REGION;

select Band2 format=$8., count(SI_PRI) as SI_PRI format=comma10., count (PRI)as PRI format=comma10.,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

group by Band2

union all

select 'TOTAL' , count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Si

;

end;

i=i+1;

Quit;

Thanks,

Nazanin

---

[NazaninSAS]

Thanks a lot Reeza,

I used Proc Report, and it was so fast and efficient.

regards,

Nazanin