Elenakretova23

SQL Aggregations & DataSet(Row & colummn operations) in SAS

Discussion created by Elenakretova23 on Dec 10, 2017

Aggregation functions:

 

Using aggregation functions, we can do  arithmetic manipulation in SQL. Using aggregate function, we can do column and row wise analysis.

  • SQL produces each and every result in column wise.
  • Aggregate functions are called summarize functions.

Ex:

Data medi;

Input Gid $ week 1 drug1 $ sub1

Week2 drug2  $ sub2;

Cards;

G100  3  col5mg  56   6   col10mg  40

G200  3  col5mg  50   6   col10mg  45

G300  3  col5mg  60   6   col10mg  49;

Proc sql;

Select Gid, week1,drug1,sub1,

Sum?(sub1) as total,

Mean(sub1) as avgsub,

Max(sub1)as maxsub from

Medi;

Quit;

/* row wise analysis*/

Ex:

Proc sql;

Select * sum(sub1,sub2)as total

From medi;

Quit;

 

Group by clause:

It can be used to do grouping analysis.

Ex:

Data clinical;

Input center $ trail $

Sub adsub;

Cards;

Appolo   phase1  67  12

Nims      phase1    78  14

Care        phase1  34  10

Appolo   phase2  267  22

Nims      phase2    178  14

Care        phase2  2 34  40

;

Proc sql;

Select  center, trail,sub,sum(sub)

As total

From clinical

Group by center;

Quit;

 

Having clause:

Work like a where clause if your want to do grouping analysis based on condition we use having clause.

Ex:

Proc sql;

Select  center, trail,sub,sum(sub)

As total

From clinical

Group by center  having center;

Not in (‘care’);

Quit;

Count functions:

It can be used to report frequency analysis. If we use * as the argument in count function , ots report number of observation generated by the current query statement.

Proc sql;

Select   count(*) as obs

From clinical

Quit;

  • If we use variable as argument, it reports number of non missing values generated by the required query statement. Clinical sas training
/* no. of group participated in each and every trail*/

Ex:

Proc sql;

Select   trail,count(trail) as obs

From clinical  group by trail;

Quit;

/* each center conducted each trail no. of times*/

Proc sql;

Select   center, trail,

count(center) as obs

From clinical group by

Center, trail;

Quit;

/* Total no. of patients participated in each trail and each center */

Ex:

Proc sql;

Select   center, trail,

count(center), sum (sub )as obs

From clinical group by

Center, trail;

Quit;

Ex:

Proc sql;

Select   center, trail,

count(center) as obs, sum(sub)

as totsub

From clinical group by

Center, trail;

Quit;

 

Distinct function:

It can be used to report unique values from the required variables

Syntax:

  Distinct (argument)

Ex:

Proc sql;

Select distinct (center) as

Cenlist from clinical;

Quit;

Ex:

Proc sql;

Select distinct (trail) as

traillist from clinical;

Quit;

 

 

Outcomes