topic Re: Proc Means to SQL code in SAS Procedures

Proc Means to SQL code

Aanch16167 — Wed, 13 May 2015 09:03:20 GMT

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;

Re: Proc Means to SQL code

Steelers_In_DC — Wed, 13 May 2015 14:32:12 GMT

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.

Re: Proc Means to SQL code

ballardw — Wed, 13 May 2015 15:35:40 GMT

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.

Re: Proc Means to SQL code

CTorres — Wed, 13 May 2015 20:21:47 GMT

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

Re: Proc Means to SQL code

CTorres — Wed, 13 May 2015 20:59:23 GMT

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

Re: Proc Means to SQL code

SASKiwi — Wed, 13 May 2015 21:24:12 GMT

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.

Re: Proc Means to SQL code

Patrick — Wed, 13 May 2015 21:39:33 GMT

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#p0rbwzwi5gyyxwn1irfgkjgprt1p.htm

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;