22.SQL LABELS AND FORMATS

                               SQL LABELS AND FORMATS

GROUP BY:::

--->IT WILL GROUP THE DATA ACCORDING WITH VARIABLES/COLUMNS

--->THE OBSERVATIONS VALUES WILL BE ARRANGED IN "ALPHABETICAL ORDER"

PROC SQL;

SELECT *

FROM SASHELP.CLASS

GROUP BY AGE;

QUIT;

PROC SQL;

SELECT *

FROM SASHELP.CLASS

GROUP BY SEX;

QUIT;

PROC SQL;

SELECT GENDER,COUNTRY

FROM PROG1.SALES

GROUP BY GENDER,COUNTRY;

QUIT;

HAVING::

--->INCLUDING/PRESENTING THE DATA

--->THIS HAVING WILL WORK WITH "GROUP BY" STATEMENT/

        WE CAN USE THESE TWO STATEMENTS AT A TIME

--->IF U USE "ORDER  BY", "GROUP BY" WILL NOT WORK

PROC SQL;

SELECT GENDER,COUNTRY,SALARY

FROM PROG1.SALES

GROUP BY GENDER,COUNTRY

HAVING GENDER;

QUIT;

PROC SQL;

SELECT *

FROM SASHELP.CLASS

GROUP BY SEX

HAVING AGE > 14;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE

FROM SASHELP.CLASS

GROUP BY SEX

HAVING NAME;

QUIT;

PROC SQL;

SELECT *

FROM SASHELP.CARS

WHERE TYPE EQ 'SUV'

GROUP BY MSRP

HAVING TYPE;

QUIT;

**************TO ELIMINATE THE DUPLICATES IN SQL?*********

--->USE "DISTINCT" OPTION TO REMOVE THE DUPLICATES

--->LIKE A "NODUPKEY" IN PROC SORT

--->IF WE MENTIONED ANY OTHER VARIABLES IN DISTINCT OPTION ACCORDINGLY IT WILL GIVE  OBSERVSATIONS(LIKE "NODUP")

PROC SQL;

CREATE TABLE SAS12 AS SELECT DISTINCT AGE

FROM SASHELP.CLASS;

QUIT;

PROC SORT DATA=SASHELP.CLASS NODUPKEY OUT=SOORTYY;

BY AGE;

RUN;

IF TWO VARIABLES MENTIONED ACTS LIKE A "NODUP"

PROC SQL;

CREATE TABLE SAS123 AS SELECT DISTINCT AGE,SEX  

FROM SASHELP.CLASS;

QUIT;

PROC SORT DATA=SASHELP.CLASS NODUP OUT=SORTTE;

BY AGE;

RUN;

--->FOR SINGLE VARIABLE ACTS LIKE A "NODUPKEY" IN PROC SORT

PROC SQL;

CREATE TABLE SAS1 AS SELECT DISTINCT AGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

CREATE TABLE SAS21 AS SELECT DISTINCT SEX

FROM SASHELP.CLASS;

QUIT;

LABELS AND FORMATS:::::::

--->LABELS::: WE CHANGE THE APPEARANCE OF COLUMNS

--->FORMAT::: WE CAN CHANGE APPEARANCE COLUMNS/VARIABLES VALUE

**THESE LABELS AND FORMATS USED IN "SELECT STATEMENT"

PROC SQL;

SELECT EMPID LABEL='EMPLOYE ID NUMBER',

       JOBCODE LABEL='EMP DISGINATION',

SALARY LABEL='EMP SALARY'

FROM SQL.PAYROLLMASTER

WHERE JOBCODE CONTAINS 'PT'

ORDER BY SALARY;

QUIT;

PROC SQL;

SELECT NAME LABEL='STUDENT NAME',

       SEX LABEL='GENDER',

AGE LABEL='STUDENT AGE'

FROM SASHELP.CLASS

WHERE AGE > 13

GROUP BY SEX;

QUIT;

--->FORMAT::: WE CAN CHANGE APPERANCE COL VALUE

DATA;

SET;

LABEL SAL;

FORMAT SAL;

RUN;

PROC SQL;

SELECT EMPID LABEL='EMPLOYE ID NUMBER',

                  JOBCODE LABEL='EMP DISGINATION',

   DATEOFBIRTH LABEL='EMPLOYE BIRTHDAY' FORMAT=DDMMYY10.,

   MEAN(SALARY) LABEL='EMP SALARY' FORMAT=COMMA15.3

FROM SQL.PAYROLLMASTER

WHERE JOBCODE CONTAINS 'PT'

ORDER BY SALARY;

QUIT;

PROC SQL;

SELECT EMPID LABEL='EMPLOYE ID NUMBER',

       JOBCODE LABEL='EMP DISGINATION',

   DATEOFBIRTH LABEL='EMPLOYE BIRTHDAY' FORMAT=WORDDATE.,

   DATEOFHIRE LABEL='JOINING DATE' FORMAT=WEEKDATE.,

       SALARY LABEL='EMP SALARY' FORMAT=EUROX12.2

FROM SQL.PAYROLLMASTER

WHERE JOBCODE CONTAINS 'PT'

ORDER BY SALARY;

QUIT;

PROC SQL;

SELECT SALARY LABEL='EMP SAL' FORMAT=DOLLAR12.2,

               GENDER LABEL='SEX',

   BIRTH_DATE LABEL='EMP DOB' FORMAT=DATE9.,

   HIRE_DATE LABEL='DATE OF JOINING' FORMAT=WORDDATE.

FROM PROG1.SALES;

QUIT;

IF YOU  WANT TO ADD A GIVEN OBSERVATIONS  IN SELECT STATEMENT USE ":"(COLON)

PROC SQL;

SELECT SALARY LABEL='EMP SAL' FORMAT=DOLLAR12.2, 'GENDER IS:',

       GENDER LABEL='SEX', 'DOB OF EMP:',

   BIRTH_DATE LABEL='EMP DOB' FORMAT=DATE9., 'JOINING:',

   HIRE_DATE LABEL='DATE OF JOINING' FORMAT=WORDDATE.

FROM PROG1.SALES;

QUIT;

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

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

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