SAS/SQL Basics

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*creating table*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

proc sql ;
create table student_data 
(name char (35),
class char (10),
marks num (10),
Total num (10));
quit;


           /*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Value insertion*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;


proc sql;
insert into student_data values('ankit1','B.Tech',5808,10000);
insert into student_data values('ankit2','B.Tech',5802,10000);
insert into student_data values('ankit3','B.Tech',5801,10000);
insert into student_data values('ankit4','B.Tech',5804,10000);
insert into student_data values('ankit5','B.Tech',5803,10000);
insert into student_data values('ankit6','B.Tech',5844,10000);
insert into student_data values('ankit7','B.Tech',5823,10000);
insert into student_data values('ankit8','B.Tech',2135,10000);
insert into student_data values('ankit9','B.Tech',4653,5000);
insert into student_data values('ankit10','B.Tech',1363,5000);
insert into student_data values('ankit11','B.Tech',4561,5000);
insert into student_data values('ankit12','B.Tech',0321,5000);
insert into student_data values('ankit13','B.Tech',5123,10000);
insert into student_data values('ankit14','B.Tech',5462,10000);
insert into student_data values('ankit15','B.Tech',4513,5000);
insert into student_data values('ankit16','B.Tech',4562,5000);
insert into student_data values('ankit17','B.Tech',8432,10000);
insert into student_data values('ankit18','B.Tech',7653,10000);
insert into student_data values('ankit19','B.Tech',null,10000);
quit;

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*delete values from table*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

proc sql;
delete * from student_data ;
quit;

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Using case keyword*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*using group option*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*using order potion*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*condition in case option*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

proc sql;
create table Student_Result as
select name as Student_Name,
  (Marks * 100)/Total as Degree_Percentage,
  case 
  when Marks le 3300 then 'Fail'
  when Marks le 4500 then 'III Division'
  when Marks le 6000 then 'II Division'
  when Marks le 7500 then 'I Dividion'
  Else 'Passed with Honour'
  end as Result
from student_data
group by Result
order by Degree_Percentage;
quit;

/*/*/*/*/*use calculated option in alias name for further calculation*/*/*/*/*/;

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*calculated option*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;

proc sql;
create table cases as
select name as student_name,
  (Marks * 100)/Total as Degree_Percentage,
  (Calculated Degree_Percentage - 100) as Diffrence
from student_data;
quit;
    

/*/*/*/*/*/*/*COALESCE function for replace missing values*/*/*/*/*/*/*/;

proc sql;
create table missing as
select Name,COALESCE(Marks,0)  
from student_data;
quit;

/****************SORTSEQ=EBCDIC statement option for sorting *************************/

proc sql sortseq=ebcdic;
select * from student_data
order by Marks;
quit;

/********in option with where condition *********/

proc sql;
create table inoption as
select name,marks
from student_data
where name in ('ankit1','ankit7') 
order by marks;
quit;

/*********any condition option with where condition *************/

proc sql;
create table anyone as
select name,marks from student_data
where marks > any (select marks from inoption);
quit; 
 

/*********all condition option with where condition *********/

proc sql;
create table all as
select name,marks from student_data
where marks > all(select marks from inoption);
quit; 

/********Between condition option with where condition *********/

proc sql;
create table between as
select name,marks from student_data
where marks between 5000 and 10000;
quit; 

/***********contains condition option with where condition ************/

proc sql;
create table contains as
select name,marks from student_data
where name contains 'ankit18' ;
quit;

/*******EXISTS condition option with where condition *******/

proc sql;
create table EXISTSs as
select name,marks from student_data
where exists (select * from inoption);
quit; 

/**********Is Missing  option with where condition **********/

proc sql;
create table miss as
select name,marks from student_data
where marks is missing;
quit; 

/*/*/****LIKE operator enables you to select rows based on pattern matching*****/*/*/;

PROC SQL ;
CREATE TABLE LIKEAS AS
SELECT NAME,
  MARKS 
FROM STUDENT_DATA
WHERE NAME LIKE 'A%' OR NAME LIKE '_____1';
QUIT;

/*/*/*/*****Truncated string comparison operators are used to compare two strings*****/*/*/*/;

/*GTT=GRAEATER THEN*/
/*EQT=EQUAL TO*/
/*LTT=LESS THEN*/
/*GET=GREATER THEN EQUAL TO*/
/*NET=NOT EQUAL TO*/


PROC SQL;
CREATE TABLE TRUNCATED AS
SELECT * FROM STUDENT_DATA
WHERE NAME GTT 'ANKIT1';
QUIT;

/*finding the number of non missing values we use (COUNT,N,FREQ) options  */;

/*count = Counting of non missing values*/

/*N  = Counting of non missing values*/

/*FREQ  = Counting of non missing values*/

proc sql;
create table coun as
select COUNT(marks) from student_data;
quit;

/*/*/*/*/*/*correct sum of square (CSS) option*/*/*/*/*/*/;

/*PRT    = probability of a greater absolute value of Student’s t*/

/*STDERR = standard error of the mean*/

/*SUMWGT = sum of the WEIGHT variable values1*/

/*T      = Student’s t value for testing the hypothesis that the population mean is zero*/

/*USS    = uncorrected sum of squares*/

/*VAR    = variance*/

proc sql;
create table correct_sum_of_square as
select CSS(marks) as sum
from student_data;
quit; 

/*/*/*/*/*/Max fuction to find the maximum value in the numeric variable*/*/*/*/*/;

proc sql;
create table maxvalue AS
select name, marks,
Max(marks) as maxmarks format=comma6.
from student_data
order by marks desd; 
quit;

/*/*/*/*/*Having Option with group by and oreder by statement */*/*/*;

proc sql;
create table having as
select count(*)
from student_data
where marks is not missing 
group by name
having name in (select name from student_data where name gtt 'ankit1')
order by name;
quit;