26.UPDATING THE TABLES

                                    UPDATING THE TABLES

 

PROC SQL;

SELECT NAME,AGE

FROM SASHELP.CLASS

WHERE AGE GE 14

ORDER BY AGE

GROUP BY SEX;

QUIT;


DATA SAS;

SET SASHELP.CLASS;

RUN;

PROC PRINT;

RUN;


WE CAN ADD THE OBSERVATIONS/ROWS IN SQL:::


-->INSERT STATEMENT:: IT WILL INSERT/ADD THE ROWS IN TABLE

-->VALUE STATEMENT:: ROW/OBSERVATIONS VALUES MUST BE MENTIONED

     (IF CHARACTER:QUOTES     IF NUMBER=NO NEED QUOTES)


PROC SQL;

INSERT INTO SAS

VALUES 

("ASHOK","M",26,6,60)

VALUES

("KAMAL","M",25,6,62)

VALUES

("ADITY","M",23,5,65);

QUIT;


DATA SAS2;

SET SASHELP.CLASS;

RUN;


--->INSERTING THE ROWS BASED ON THE COLUMN NAME


PROC SQL;

INSERT INTO SAS2

(SEX,AGE)

VALUES

("M",34);

QUIT;


ALTERING THE TABLES::::


--->WE CAN ADD COLUMNS,DELETE THE EXISTING COLUMN, MODIFY THE FORMAT OF THE COLUMN


UPDATE TABLE  ::


--->MODIFY THE EXISTING COLUMN VALUE IN TABLE


DATA SAS222;

SET SASHELP.CLASS;

RUN;


PROC SQL;

ALTER TABLE SAS222 ADD SECTION CHAR(10),TOTALMARK NUM(8),DOB NUM 

            INFORMAT=DATE7. FORMAT=DATE9.;

QUIT;



UPDATE TABLE::


--->MODIFY THE EXISTING COLUMN VALUE IN TABLE


PROC SQL;

UPDATE SAS222

SET 

SECTION='SECTION A',TOTALMARK=100,DOB='02MAR22'D

WHERE AGE <14;

QUIT;


MODIFY THE TABLE::::


PROC SQL;

ALTER TABLE SAS222

MODIFY TOTALMARK DECIMAL(8,3)

FORMAT=8.3;

QUIT;



DELETE THE COLUMN::


PROC SQL;

ALTER TABLE SAS222

DROP TOTALMARK,SECTION;

QUIT;



CREATING VIEWS::


PROC SQL;

CREATE VIEW HEIGHTDAT AS SELECT MAX(HEIGHT) AS MAX_HT

FROM SASHELP.CLASS;

QUIT;


PROC SQL;

CREATE VIEW SALARYS AS SELECT SALARY 

FROM PROG1.SALES

WHERE SALARY > 50000;

QUIT;


DICTIONARY.MEMBERS

DICTIONARY .CATALOGS

DICTIONARY.LIBNAMES

DICTIONARY.INDEX

DICTIONARY.OPTIONS

DICTIONARY.COLUMNS

DICTIONARY.TITILESLUMNS

DICTIONARY.MACROS

DICTIONARY.FOOTNOTES


PROC SQL;

SELECT *

FROM DICTIONARY.MEMBERS;

QUIT;



PROC SQL;

SELECT *

FROM DICTIONARY.TABLES;

QUIT;




PROC SQL;

SELECT *

FROM DICTIONARY.COLUMNS;

QUIT;


TO REMOVE TITLE AND FOOTNOTES :::


TITLE 'SAS DATA';

FOOTNOTE 'IGAUGAGUXAX';



PROC SQL;

SELECT *

FROM DICTIONARY.TITLES;

QUIT;


PROC SQL;

SELECT *

FROM DICTIONARY.FOOTNOTES;

QUIT;



PROC SQL;

SELECT *

FROM DICTIONARY.VIEWS;

QUIT;




PROC SQL;

SELECT *

FROM DICTIONARY.MACROS;

QUIT;


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

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

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






























































































































































Comments