example-15sep10.txt

options formdlim="-";


/*  default behavior */

data test;
  input cond $ count1  cond2 $ count2  cond3 $ count3;
datalines;
a 10
a 11
a 12
b 20
b 21 
b 22
;
run;

proc print data=test;
run;


/* stop reading at end of line -
MISSOVER option, which assigns missing values to variables for records that contain no data for those variables
*/

data testA;
  infile datalines missover;
  input cond $ count1  cond2 $ count2  cond3 $ count3;
datalines;
a 10
a 11
a 12
b 20
b 21 
b 22
;
run;

proc print data=testA;
run;


/* explicitly naming variable values on different lines */

data test4;
  infile datalines missover;
  input cond $ count1 #2 @3 count2  #3 @3 count3;
datalines;
a 10
a 11
a 12
b 20
b 21
b 22
;
run;

proc print data=test4;
run;

/* why use # vs. / to specify different lines? */

data test4A;
  infile datalines missover;
  input cond $ count1 #3 @3 count3 #2 @3 count2  ;
datalines;
a 10
a 11
a 12
b 20
b 21
b 22
;
run;

proc print data=test4A;
run;

proc print data=test4A;
  var cond count1 count2 count3;
run;

/*  what if you don't have spaces in between values of variables?  A:  formatted input - MTC */

data test4B;
	input @1 cond $1 @2 count1 2. @4 cond2 $1 @5 count2 2. @7 cond3 $1 @8 count3 2.;
	datalines;
a10a11a12
b20b21b22
;
run;

proc print data=test4B;
run;

/*

REF:  SAS Certification Prep Guide:  Advanced Programming in for SAS 9

Data Step programming to manipulate data sets ...

Adding observations = concatentating data sets = adding rows (via SET)  "row binding of matrices"
  
Adding variables = merging data sets (via MERGE)    "column binding of matrices"

*/


/*

SQL is an alternative

Data processing            SAS                    SQL
file                       SAS data set           table

record                     observation            row

field                      variable               column



>>>> SQL differences from SAS ...

* executes query automatically (don't need RUN;)

* continues to run after you submit a step (need to QUIT; to stop execution)

* SQL statements are often clauses terminated by ;


>>>> SQL:  Combining tables horizontally (JOINING TABLES)

INNER JOIN = rows that match across tables

LEFT JOIN = Inner common rows + non-matching rows from left table

RIGHT JOIN = Inner common rows + non-matching rows from right table

FULL JOIN = all rows in either table


>>>> SQL:  Combining tables vertically

proc sql;
  select *
         from Table1
  set-operation
  select *
         from Table2
  ;

set-operation = { except, intersect, union, outer union }

* first 3 operations overlay columns based on position w/o regard to column names ["corr" option can be used]

* last operation (outer union does not overlay columns)


>>>> SQL vs. DATA

* SQL does not require sorted tables

* SQL does not require same column name in two tables

* SQL can use other comparison operations other than =