23.CREATING NEW VARIABLES IN SQL

                           23.CREATING NEW VARIABLES IN SQL

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IT IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYNTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATEMENT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATEMENT IN SAS LOG

--->THIS WILL USED IN COMBINING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATEMENT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

CREATING THE NEW VARIABLES/NEW COLUMNS BY USING "AS" KEYWORD

-->IN BASE SAS::IN BASE SAS BY THESE STATEMENTS WE CAN CREATE NEW VARIABLES IF-THEN(CONDITIONAL) AND ASSIGNMENT(=) AND SUM(+) 

-->IN SQL::DIRECTLY WE CAN CREATE NEW VAR IN "SELECT" STATEMENT WITH USING 

    ARITHMETIC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXISTING VARIABLES THEN WRITE  LIKE THIS

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARITHMETIC OPERATORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

:::::HOW TO CALCULATE THE AGES?

--->BY USING "INT" FUNCTION

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBSERVATIONS  

 4.FREQ:COUNT OF OBSERVATIONS   5.N   6.MIN  

  7.MAX   8.NMISS:NUMBER OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBSERVATIONS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NUMBER OF MISSING

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VARIABLES

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

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

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

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

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);

OPTIONS AND STATEMENTS USED IN SQL::::

1.NOEXEC OPTION:: 

--->IS LIKE "NOPRINT"

--->IT WILL CHECK THE SYTAX IN LOG

--->THIS  OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC MEANS DATA=SASHELP.CLASS NOPRINT;

RUN;

PROC SQL ;

SELECT *

FROM SQL.PAYROLLMASTER;

QUIT;

PROC SQL NOEXEC;

SELECT *

FROM SQL.PAYROLLMASTER;

GROUP BY GENDER;

QUIT;

2.FEEDBACK OPTION::

--->IT WILL WRITE THE "EXPANDED SELECT" STATMNT IN SAS LOG

--->THIS WILL USED IN COMBAING THE DATA/SQL JOINS

--->WE OPTION WILL MENTIONED IN "PROC SQL" STATMNT

PROC SQL FEEDBACK;

SELECT *

FROM SQL.PAYROLLMASTER; 

QUIT;

******CREATING THE NEW VAR/NEW COL BY USING "AS' KEYWORD**************;

-->IN BASE SAS::IF-THEN(CONDITINOL) AND ASSINGMENT(=) AND SUM(+) 

-->IN SQL::DIRCTLY WE CAN CREATE NEW VAR IN "SELECT" STATMNT WITH USING 

    ARTHMETC EXPRESSION USE "AS" KEYWORD

PROC SQL;

SELECT NAME,SEX,AGE AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

 

IF YOU NEED EXSITING VAR WRITE LIKE

PROC SQL;

SELECT NAME,SEX,AGE,AGE+5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

USE ALL ARTMENTIC OPARETORS

PROC SQL;

SELECT NAME,SEX,AGE,AGE*5 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT NAME,SEX,AGE,AGE-10 AS NEWAGE

FROM SASHELP.CLASS;

QUIT;

V IMP:::::HOW TO CALCULATE THE AGES?????

--->BY USING "INT" FUNCTION

BASED ON SUB CONSEERN:::HE WILL GIVE AGE INFO OR HE WILL GIVE DOB

               SOMTIMES SUB WILL NOT GIVE AGE,DOB????????

HERE AT LEAST ONE DATE HAS TO BE PRASENT WITH U:::DOB OR TRT START DATE

PROC SQL;

SELECT EMPID,JOBCODE,GENDER,DATEOFBIRTH,

       INT((TODAY()-DATEOFBIRTH)/365.25) AS EMP_AGE

FROM SQL.PAYROLLMASTER;

QUIT;

*********SUMMARY FUNCTIONS::::::::::::::

1.AVG   2.MEANS   3.COUNT:COUNT OF OBS   4.FREQ:COUNT OF OBS   5.N   6.MIN  

          7.MAX   8.NMISS:NO OF MISSING VALUES

9.STD   10.SUM    11.VAR(VARIANCE)

PROC SQL;

SELECT AVG(AGE) AS AVGAGE

FROM SASHELP.CLASS;

QUIT;

PROC SQL;

SELECT MEAN(SALARY) AS SDFGK

FROM SOMESH.SALES;

QUIT;

PROC SUMMARY DATA=SASHELP.CARS PRINT;

RUN;

PROC SQL;

SELECT COUNT(*) AS ALLOBS

FROM SASHELP.CARS;

QUIT;

PROC SQL;

SELECT FREQ(MSRP) AS ALLOBSER

FROM SASHELP.CARS;

QUIT;

DATA SWAA;

INPUT ID;

CARDS;

1

2

.

.

.

4

.

.

5

.

;

RUN;

PROC MEANS DATA=SWAA NMISS;

RUN;

COUNT:: IT WILL GIVE THE COUNT OF ALL NON-MISSING OBS 

PROC SQL;

SELECT COUNT(ID) AS COUNTOFOB

FROM SWAA;

QUIT;

NMISS:: TO FIND THE NO OF MISSINGS

PROC SQL;

SELECT NMISS(ID) AS MISSING

FROM SWAA;

QUIT;

SUM:::::CALCULATE THE TOTAL OF GIVEN VAR

PROC SQL;

SELECT SUM(HEIGHT,WEIGHT) AS TOTALWH

FROM SASHELP.CLASS;

QUIT;

:::::::::::::

SQL JOINS

INT((TODAY()-TRTSTRTDT)/365.25);