Data Management Quick Comparison 1

SAS

Selecting -All Fields

SQL

Select * from mydata;

SAS

Proc print data = mydata;

– One Field

SQL

Select col1 from mydata

Proc print data = mydata (keep = col1);

– Subset

SQL

Select * from mydata where col2 = 2786586641

SAS

Proc print data = mydata (where = (col2 =2786586641);

Sorting

SQL

select * from mydata sort by col2

SAS

Proc sort data = mydata; by col2;

Join

SQL

Select * from mydata_2 as mydt inner join mydata_lkup lkup on lkup.col1 = mydt.col2

SAS

Proc sort data = mydata; by col2;
Proc sort data = mydata_lkup; 
    by col1; 

data tmp;
     merge mydata (in = _mydt rename col2 _lnk) 
        mydata_lkup( in = _lkup rename col1 = _lnk);
             by _lnk;
run; 

Sample

SQL

Select top 10 from mydata

SAS

Proc print data = mydata (obs =10);

Aggregate Analysis

SQL

Select col2, count(*) from mydata_lkup group by col2

SAS

proc freq data = mydata_lkup;

tables col2;
run;

______or_________________

Proc sort data = mydata_lkup;
By col2;

Data agg;
    set mydata;
       by col2;
         retain count;
     If first.col2 eq 1 then do;
         Count = 0;
    end;

Count = count +1;

If last.col2;
     keep col2 count;
quit;
run;

Unique

SQL

Select distinct col2 from mydata_lkup;

SAS

Proc sort data = mydata_lkup nodupkey;
By col2;