Proc Means to SQL code

[Aanch16167]

sql
I am trying to convert the following code to SQL.

Any help on this would be appreciated.

proc means data = temp missing no print chartype;

by source Year;

class cltname incentive;

va Elig overall initial consecutive;

output out = smry sum=;

run;

[Steelers_In_DC]

If you can provide a small sample of the data so anyone trying to help can see the input/output that would be helpful.  Or use a sashelp. dataset for someone to manipulate in the same way.

[ballardw]

If you want the _type_ variable and associated behavior I would ask why? In SQL you will likely have to create 4 separate summary sets and then combine them. And if you add more class variables the number of sets goes up.

[CTorres]

Hi Aanch16167,

The following example shows how to convert a PROC MEANS to PROC SQL. You can use it as starting point to create your own solution:

data grade;

   input Name $ 1-8 Gender $ 11 Status $13 Year $ 15-16

         Section $ 18 Score 20-21 FinalGrade 23-24;

   datalines;

Abbott    F 2 97 A 90 87

Branford  M 1 98 A 92 97

Crandell  M 2 98 B 81 71

Dennison  M 1 97 A 85 72

Edgar     F 1 98 B 89 80

Faust     M 1 97 B 78 73

Greeley   F 2 97 A 82 91

Hart      F 1 98 B 84 80

Isley     M 2 97 A 88 86

Jasper    M 1 97 B 91 93

Charly    M 1    B 90 90

Sandra    F 2 98   95 88

;

run;

proc sort data=Grade out=GradeBySection;

   by section;

run;

/****** PROC MEANS *******/

proc means data=GradeBySection missing noprint chartype;

   by Section;

   var Score FinalGrade;

   class Status Year;

   output out=Want_Means sum=;

run;

proc sort data=Want_Means;

  by Section Status Year _TYPE_;

run;

/******* PROC SQL  *******/

proc sql noprint;

  create view type11 as

  select distinct Section, Status, Year, '11' as _TYPE_,

      count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

  from grade

  group by Section, Status, Year;

  create view type10 as

  select distinct Section, Status, '  ' as Year, '10' as _TYPE_,

      count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

  from grade

  group by Section, Status;

  create view type01 as

  select distinct Section, ' ' as Status, Year, '01' as _TYPE_,

      count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

  from grade

  group by Section, year;

  create view type00 as

  select distinct Section, ' ' as Status, '  ' as Year, '00' as _TYPE_,

      count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

  from grade

  group by Section;

  create table Want_SQL as

  select * from type11

  union all

  select * from type10

  union all

  select * from type01

  union all

  select * from type00

  order by 1,2,3,4;

quit;

/****** Compare the results *******************/

proc compare data=Want_Means compare=Want_SQL;

run;

I hope this helps,

CTorres

[CTorres]

So, the following would be the solution (untested) for your proc means assuming the class variables are character type:

proc sql noprint;

  create view type11 as

  select distinct source, Year,                   /* from by statement in Proc means */

     cltname, incentive,                          /* from class statement in Proc means */

     '11' as _TYPE_, count(*) as _FREQ_,          /* _TYPE_ and _FREQ_ variables  */

     Sum(Elig) as ELIG, sum(overall) as overal,   /* SUM statistics for var statement */  

     Sum(initial) as initial, sum(consecutive) as conservative

  from temp

  group by  source, Year, cltname, incentive;     /* from both by and class statements  */

  create view type10 as

  select distinct source, Year,

     cltname, ' ' as incentive,

     '10' as _TYPE_, count(*) as _FREQ_,

     Sum(Elig) as ELIG, um(overall) as overal,

     Sum(initial) as initial, sum(consecutive) as conservative

  from temp

  group by  source, Year, cltname;

  create view type01 as

  select distinct source, Year,

     ' ' as cltname, incentive,

     '01' as _TYPE_, count(*) as _FREQ_,

     Sum(Elig) as ELIG, um(overall) as overal,

     Sum(initial) as initial, sum(consecutive) as conservative

  from temp

  group by  source, Year, incentive;

  create view type00 as

  select distinct source, Year,

     ' ' as cltname, ' ' as incentive,

     '00' as _TYPE_, count(*) as _FREQ_,

     Sum(Elig) as ELIG, um(overall) as overal,

     Sum(initial) as initial, sum(consecutive) as conservative

  from temp

  group by  source, Year;

  create table smry as

  select * from type11

  union all

  select * from type10

  union all

  select * from type01

  union all

  select * from type00

  order by 1,2,3,4;

quit;

CTorres

[SASKiwi]

In my humble opinion using SQL instead of PROC MEANS is only worthwhile if you are using NWAY to get the lowest level or only one level of stats. Otherwise the cure is worse than the disease....

Note that if you are wanting to run this on a non-SAS database, SAS will translate the MEANS code into SQL automatically.

[Patrick]

Proc Means is one of the procedures that are enhanced for in-database processing so may be translating the logic into SQL won't give you anything else than more work and higher complexity code.

http://support.sas.com/documentation/cdl/en/proc/67327/HTML/default/viewer.htm#p0rbwzwi5gyyxwn1irfgk...

Haven't tried it but it's worth a shot: You could define below options before running your Proc Means code. Proc Means being an in-database enhanced procedures SAS will try to convert the proc syntax into SQL code and send it to the database. The options below will show you the SQL code generated in the SAS log. This will show you if SAS could translate the Proc Means code fully into SQL and if you still want to go for a SQL replacement then the code generated would also be a very good starting point for you (just copy/paste it).

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;