Expecting summary output by proc sql similar to proc means

[s_manoj]

Hi all,

   If we run proc means on sashelp.cars , we get an output as mentioned below.

SAS Output

but am trying to get same summary output with help of SQL,

here's my code:

%macro summary;
%global name_list;
proc sql noprint;
select name, count(name) 
into :name_list separated by " " ,
     :n
from dictionary.columns
where memname = "CARS" & type = "num";

create table sumry  as
%do i = 1 %to &n;
%let name = %scan(&name_list, &i);
   select 
    "&name" as variable,
    count(&name) as n,
	min(&name) as minimum,
	max(&name) as maximum,
	mean(&name) as average,
	std(&name) as standard_dev
from sashelp.cars
group by variable;
%end;
quit;
%mend summary;
option symbolgen mlogic;
%summary;

when I run this code am getting output with only one row and not able to get summary results for all other variables, can someone help me with this code to get the same output as proc means(label column not required).

Thank you in advance.

[PaigeMiller]

Well, the simplest answer is to use PROC MEANS and not PROC SUMMARY. Why re-program something that is already programmed and easy to use and contains the appropriate error checking and contains a gazillion other features? You are just making your life more difficult.

[s_manoj]

Yes we can get summary by proc means just like that, but personally i taught to generate the same output by sql so that i can dig into some more concepts

[PaigeMiller]

---

@s_manoj wrote:
Yes we can get summary by proc means just like that, but personally i taught to generate the same output by sql so that i can dig into some more concepts

---

There's certainly a lot to learn in PROC SQL. Learning how to make it replicate PROC MEANS ought not to be something you need to learn, and ought not to be something you spend time on.

And don't forget Maxim 14. (I don't have 14 Maxim's, but @Kurt_Bremser does) Make your life easier. Use PROC SQL for the things it is good at. Use PROC MEANS for the things it is good at. 

[SuryaKiran]

Wondering why you want in PROC SQL if there is PROC MEANS. Anyway here is my solution.

%macro summary;
%global name_list;
/* If you don't mention Libname here, all the library metadata need to be read */
/* Mentioning the libaname will increase performance, only one library metadata is read */
/* Helpful if you have many libraries */
proc sql noprint;
select name, count(name) 
into :name_list separated by " " ,
     :n
from dictionary.columns
where libname="SASHELP" & memname = "CARS" & type = "num";

%do i = 1 %to %sysfunc(countw("&name_list"));
%let name = %scan(&name_list, &i);
create table sumry_&i  as
   select 
    "&name" as variable,
    count(&name) as n,
	min(&name) as minimum,
	max(&name) as maximum,
	mean(&name) as average,
	std(&name) as standard_dev
from sashelp.cars
group by variable;
%end;
quit;
%let count=%sysfunc(countw("&name_list"));
data sumry;
Length variable $30.;
set sumry_1-sumry_&count;
run;
%mend summary;
option symbolgen mlogic;
%summary;

Your DO LOOP will create single dataset by updating in each iteration in do loop. Don't forget to add libname when querying dictionary tables, it will increase performance if there are more libraries defined.

[s_manoj]

Thank you very much,
and i want to know,
is there any way to control output(implicit/explicit) in %do loop, as we do in base DO LOOP

[ballardw]

---

@s_manoj wrote:
Thank you very much,
and i want to know,
is there any way to control output(implicit/explicit) in %do loop, as we do in base DO LOOP

---

Use options mprint to examine the code you are creating as that is what the macro language does: create code.

Since macro %do loops are frequently not part of a data step there is no "output" control. You would have to include the code to create such. And your context would be very important as data step is the only place such control is used.

[Kurt_Bremser]

Maxim 14.

SQL is not the right tool for this.

[novinosrin]

@Kurt_Bremser  Absolutely. My class mates at my uni have a suggestion. Would it be possible for us to have Maxims of Maximally Efficient SAS Programmers link to appear on the front page? 

To me it makes sense, for new members et al to easily view the best practices rather than having to click on a post and look through the footnotes and then go to the page. Of course I know that's in the library as  we are regulars here, but 2 cents for the sake of new folks. A lot of them do not even know about it.

I should suggest this to our community managers but I wanted your thought on the same in the first place.

[Kurt_Bremser]

---

@novinosrin wrote:

@Kurt_Bremser  Absolutely. My class mates at my uni have a suggestion. Would it be possible for us to have Maxims of Maximally Efficient SAS Programmers link to appear on the front page? 

To me it makes sense, for new members et al to easily view the best practices rather than having to click on a post and look through the footnotes and then go to the page. Of course I know that's in the library as  we are regulars here, but 2 cents for the sake of new folks. A lot of them do not even know about it.

I should suggest this to our community managers but I wanted your thought on the same in the first place.

---

Put a link up anywhere you want. I wrote the Maxims with the explicit intention that they be seen as much as possible.

I think I will have to check with @ChrisHemedinger if it's allowed to stick a CC-BY-SA license to it. I have no problem if someone picks up the ball and runs with it, but I'd like to be mentioned 😉