BookmarkSubscribeRSS Feed
Aanch16167
Calcite | Level 5

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;

6 REPLIES 6
Steelers_In_DC
Barite | Level 11

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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
PROC Star

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4542 views
  • 0 likes
  • 6 in conversation