21.SAS SQL INTRODUCTION

                                            SQL INTRODUCTION

                        *******STRUCTURED QUERY LANGUAGE(SQL)******

     -->ANSI(AMERICAN NONSTANDARD INSTITUTE)

     -->IBM

     -->DEVELOPED BY CODD 

     -->HERE WE USE 'QUIT' AT THE END OF THE CODE

TERMINOLOGY:::

 BASE SAS                    ADVANCE SAS SQL                        EXCEL

->DATASET                       ->TABLE                                       ->TABLE

->OBSERVATION             ->ROWS                                         ->ROWS

->VARIABLES                  ->COLUMNS                                 ->COLUMNS

*****SAS SOFTWARE ENABLING THE SQL IN SAS PLATFORM***********

SQL LANGUAGE:::NEED A SEPARATE "SERVER" TO LOGIN THE SQL DB

MAJOR ADVANTAGES OF SQL:::

--->HERE WE ARE NOT USING "PROC PRINT"

--->NOT USING "PROC SORT"

--->WE CAN COMBINE "256" TABLES/DATASET WITHOUT SORTING THE DATA

DIFFERENCE  B/W DATASTEP MERGE AND SQL JOINS?

DATASTEP MERGE:: NEED TO SORT THE DATA AND CAN COMBINE 2 OR MORE DATASETS

SQL JOINS:::NO NEED OF SORT THE DATA CAN COMBINE 256 TABLES AT A TIME

BASE SAS:::SYNTAX

DATA XXX;

SET SSS;

RUN;

PROC PRINT(MEANS,FREQ);

RUN;

IN ADVANCE SAS:SYNTAX

PROC SQL;

QUIT;

/*BASIC SQL SYNTAX::::*/    

PROC SQL;

SELECT:WILL GIVE COLUMN/VARIABLE INFO ALONG WITH 'COMMA'/EACH VARIABLE/COLUMN SEPARATED WITH 'COMMA'

FROM:WE CAN SELECT THE TABLE/DATASET

WHERE::WE CAN SUBSET THE ROWS/OBSERVARTIONS(ALL WHERE OPERATORS WILL WORK HERE)

ORDER BY:TO KEEPING ORDER THE DATA(DEFAULT 'ASCENDING')

                   IF YOU WANT DESCENDING(DESC) ADD 'DESC'

GROUP BY:WILL GROUP THE DATA(10:F M) (ONLY M AND F)

HAVING::TO REPRESENT THE DATA/TO INFORM THE DATA IS HAVING IN TABLE;

***FOR THE LAST STATEMENT ONLY NEED TO ADD "SEMICOLON(;)" AND

"QUIT";

FOR BASE SAS:

LIBNAME PROG1 'PATH';

THESE SQL WILL WORK ON THE EXISTING DATA WHICH IS PRESENT IN LIBRARY'S(PERMANENT OR TEMPORARY)

1.WITHOUT COMMA IN SELECT STATEMENT WILL GIVE 'ERRORS'

PROC SQL;

SELECT NAME AGE SEX

FROM SASHELP.CLASS;

QUIT;

2.WITH COMMA NO ERRORS

PROC SQL;

SELECT NAME,AGE,SEX

FROM SASHELP.CLASS;

QUIT;

IN DATA STEP:

DATA SSA;

SET SASHELP.CLASS;

KEEP NAME AGE SEX;

RUN;

PROC PRINT;RUN;

3.FOR SELECTING THE ALL VARIABLES FROM TABLE/DATASET USE AS-TRICK(*) IN SELECT STATEMENT

PROC SQL;

SELECT *

FROM SASHELP.CARS;

QUIT;

4.WE CAN KEEP THE COLUMNS/VARIABLES IN ORDER OR NOT IN ORDER ALSO

PROC SQL;

SELECT HEIGHT,NAME

FROM SASHELP.CLASS;

QUIT;

5.TO CREATE A NEW DATASET/TABLE IN LIBRARY USE "CREATE TABLE" WITH REFERENCE OF "AS" KEYWORD

PROC SQL;

CREATE TABLE NANI AS SELECT AGE,SEX,HEIGHT

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

CREATE TABLE NANI1 AS SELECT *

FROM SASHELP.CARS;

QUIT;

6.TO ELIMINATE OR INCLUDE THE COLUMN/VARIABLE INFO IN TABLE/DATASET

PROC SQL;

CREATE TABLE NANI2 (DROP=HEIGHT)

AS SELECT *

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

CREATE TABLE NANI3 (KEEP=NAME HEIGHT)

AS SELECT *

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,AGE

FROM SASHELP.CLASS;

QUIT;

TO SUBSET THE ROWS/OBSERVATIONS USE "WHERE STATEMENT AND ALL OPERATORS"

PROC SQL;

SELECT *

FROM PROG1.SALES

WHERE GENDER='M';

QUIT;

PROC SQL;

SELECT *

FROM PROG1.SALES

WHERE GENDER='F';

QUIT;

PROC SQL;

CREATE TABLE HEMALATHA AS SELECT *

FROM PROG1.SALES

WHERE GENDER='F';

QUIT;

PROC SQL;

SELECT *

FROM PROG1.SALES

WHERE SALARY > 50000;

QUIT;

PROC SQL;

SELECT *

FROM PROG1.SALES

WHERE SALARY >= 50000;

QUIT;

PROC SQL;

SELECT *

FROM PROG1.SALES

WHERE SALARY <= 50000;

QUIT;

*****HERE WE CAN APPLY ALL WHERE OPERATORS TO PROC SQL;

ORDER BY::: KEEPING THE DATA INTO ORDER

   --->DEFAULT WE CAN USE THE DATE IN  "ASCENDING" MANNER

   --->IF WE WANT IN  "DESCENDING"(DESC) DATA THEN WE HAVE TO GIVE "DESC"

PROC SQL;

SELECT *

FROM SASHELP.CLASS

WHERE SEX='M'

ORDER BY AGE;

QUIT;

IF YOU NEED DESCENDING ORDER

PROC SQL;

SELECT *

FROM SASHELP.CLASS

WHERE SEX='F'

ORDER BY AGE DESC;

QUIT;

DATA SAIII;

SET SASHELP.CLASS;

WHERE SEX='F';

RUN;

PROC PRINT;

RUN;

PROC SORT DATA=SAIII;

BY AGE;

RUN;

--PLEASE FOLLOW THE BLOG FOR MORE INFO....

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

   CLICK HERE: https://t.me/SasAll4You