*Copyright @ www.mycsg.in;

Create a copy of an existing dataset

proc sql;
    create table class2 as
    select *
    from class;
quit;

Add new variables or modify existing variables

proc sql;
    create table class2 as
        select *,age*12 as agemon,"Class 9" as class
        from class;
quit;

Select only required variables

proc sql;
    create table ahw as
        select name,age,height,weight
        from class;
quit;

Select only required records

proc sql;
    create table males as
        select *
        from class
        where sex="M";
quit;

Sort the observations by values in one variable

proc sql;
    create table sorted as
    select *
    from class
    order by age;
quit;

Sort the observations by values in two variables

proc sql;
    create table sorted as
    select *
    from class
    order by age,height;
quit;

Sort the observations by values in descending order in one variable

proc sql;
    create table sorted as
    select *
    from class
    order by age desc;
quit;

Sort the observations by values in descending order in two variables

proc sql;
    create table sorted as
    select *
    from class
    order by age desc, height desc;
quit;

Count the number of records

Count the number of records with each unique value in sex variable

proc sql;
    create table counts01 as
    select sex,count(*) as count
    from class
    group by sex;
quit;

Report the number of records within each sex,age combination

proc sql;
    create table counts01 as
    select sex,age,count(*) as count
    from class
    group by sex,age;
quit;

Fetch descriptive statistics for numeric variables

Get the mean, standard deviation, minimum, maximum values of height within males and females

proc sql;
    create table stats01 as
        select sex
        ,mean(height) as mean
        , std(height) as sd
        , min(height) as min
        , max(height) as max
 
        from class
        group by sex;
quit;

Create macro variables

Create two macro variables named n1 and n2 to store the number of females and males from class dataset

proc sql noprint;
    select count(*) into :n1-:n2 from class group by sex;
quit;
 
%put &=n1 &=n2;

Create two macro variables named n1 to n6 to store the number of records in each age value from class dataset

proc sql noprint;
    select count(*) into :n1-:n6 from class group by age;
quit;
 
%put &=n1 &=n2 &=n3 &=n4 &=n5 &=n6;
 
proc sql;
    create table counts01 as
        select age,count(*) as count
        from class
        group by age;
quit;

Create two macro variables named fcount and mcount to store the number of females and males from class dataset

proc sql noprint;
    select count(*) into :mcount from class where sex="M";
    select count(*) into :fcount from class where sex="F";
quit;
 
%put &=mcount &=fcount;