Introduction

Data types : In SAS there are just two data types:

Numeric data can contain plus signs (+), minus signs (-), decimal points (.), or E for scientific notation.

Character data are everything else. They may contain numerals, Letters, or special characters (such as $ or !) and can be up to 32,767 characters long.

***************************Variables and observations************************

************************************Missing data*********************************

Rules for SAS Names:SAS data set namescan be 1 to 32 characters longmust begin with a letter (A–Z, either uppercase or lowercase) or an underscore (_)can continue with any combination of numbers, letters, or underscores.

Layout for SAS Programs:SAS statements are in free format. This means thatthey can begin and end anywhere on a lineone statement can continue over several linesseveral statements can be on a line.

Statement: A language who follows the sas rules before semicolon.

Program: A group of statement is called program.

DATA steps: begin with DATA statements read and modify data create a SAS data set

PROC (procedure) steps: begin with PROC statements perform specific analysis or function produce results or report

Statements Sample Program Code DATA statement : data clinic.admit1; SET statement : set clinic.admit; RUN statement : run; PROC PRINT statement : proc print data=clinic.admit2; RUN statement : run;

Comment To make your programs more understandable, you can insert comments into your programs.

/* Print the results */

SAS Libraries:Sashelp is a permanent library that contains sample data and other files that control howSAS works at your site. This is a read-only library.

Sasuser is a permanent library that contains SAS files in the Profile catalog that storeyour personal settings. This is also a convenient place to store your own files.

Work is a temporary library for files that do not need to be saved from session to session.

The SAS Windows:Editor window: You can use it to type in, edit, and submit SAS programs as well as edit other text files such as raw data files. In Windows operating environments, the default editor is the Enhanced Editor. The Enhanced Editor is syntax sensitive and color codes your programs making it easier to read and find mistakes.

Log window contains notes about your SAS session, and after you submit a SAS program,any notes, errors, or warnings associated with your program as well as the program statementsthemselves will appear in the Log window.

Output If your program generates any printable results, then they will appear in the Output window.

Results The Results window is like a table of contents for your Output window; the resultstree lists each part of your results in an outline form.

Explorer The Explorer window gives you easy access to your SAS files and libraries.

Rawdata:Internal raw data If you type raw data directly in your SAS program, then the dataare internal to your program. Use the DATALINES statement to indicate internal data.

External raw data files Usually you will want to keep data in external files, separating thedata from the program. This eliminates the chance that data will accidentally be altered whenyou are editing your SAS program. Use the INFILE statement to tell SAS the filenameand path, if appropriate, of the external file containing the data. The INFILE statement followsthe DATA statement and must precede the INPUT statement. After the INFILE keyword, the

Ex: Data new; Infile ‘path’; Input var-list; Run;

Common options:

CENTER | NOCENTER Controls whether output are centered or left-justified. Default: CENTER.

DATE | NODATE Controls whether or not today’s date will appear at the top of each page of output. Default: DATE.

LINESIZE = n Controls the maximum length of output lines. Possible values for n are 64 to 256. Default varies.

NUMBER | NONUMBER Controls whether or not page numbers appear on each page of SAS output. Default: NUMBER.

PAGENO = n Starts numbering output pages with n. Default is 1.

PAGESIZE = n Controls the maximum number of lines per page of output. Possible values for n are 15 to 32767. Default varies.

RIGHTMARGIN = n Specifies size of margin (such as 0.75in or 2cm) to be used for

LEFTMARGIN = n printing output. Default: 0.00in.

YEARCUTOFF = yyyy Specifies the first year in a hundred-year span for interpreting two-digit dates. Default: 1920.

Practicals

Create data:

Options nonumber nodate pagesize=15;Data new; /*create “new” data*/Set sashelp.class; /*read “class” data in “sashelp” library*/Run;

Proc print data=new; /*print the output*/Run;

Options linesize=200;Data sasuser.new;Set sashelp.class;Run;Proc print data=sasuser.new; /*print the output*/Run;

Data z x c v b n m;Set sashelp.class;Run;

data sv;input name$1-2 age 3-4 sex$5 pos 6;cards;aa26m1ab25m2ac21f3ad28f4;proc print;run;

data a;input name$ age pos sex$;cards;

as 12 1 msd 21 2 f fg 32 3 fwe 22 4 m;proc print;run;

data b;input @1 name$ @5 age;cards;ase 12sd 21fgef 32we 22;proc print;run;

data c;input @1 name$ @5 age @8sex$;cards;ase 12 msd 21 ffgef 32 mwe 22 f;proc print;run;

data d;input name$1-4 age 5-7 sex$8-9;cards;ase 12 msd 21 ffgef 32 mwe 22 f;proc print;run;

options yearcutoff=1900;data yrcutof;input date ddmmyy8.;format date ddmmyy10.;cards;21/02/4521/02/1521/02/1981;proc print;run; Chapter-2

Using a FILENAME StatementBefore you can read your raw data, you must point to the location of the external file that containsthe data. You use the FILENAME statement to point to this location.To Do This Use This SASStatementExampleReference a SAS data library

LIBNAME statement

libname libref 'SAS-datalibrary';

Reference an external file FILENAME statement

filename tests 'c:\users\tmill.dat';

Informat: A informat is an instruction that sas uses to read the data value into the variable

Format: A format is an instruction that sas uses to write the data value into the variable

Ex:

*Informat format;

Data inform;Input date ;Informat date date9.;Format date date9.;Cards;21feb1981;Proc print;run;

proc print data= sashelp.class(firstobs=2 obs=5);run;

Column input: we use column input when data are in fixed column.

List input: when data are not in fixed column.

1. Colon (:):- it used when values are greater then 8 bytes! 2. Ampersand (&):- it is used when data values contain embedded blank

Format input: we used format input when we know the starting point of values but data should be in fixed column

Standard and Nonstandard Numeric Data

Standard numeric data values can contain onlynumbersdecimal pointsnumbers in scientific or E-notation (2.3E4, for example)plus or minus signs.

Nonstandard numeric data includesvalues that contain special characters, such as percent signs (%), dollar signs ($), and commas (,)date and time valuesdata in fraction, integer binary, real binary, and hexadecimal forms.

Format Specifies These Values ExampleCOMMAw.d that contain commas and decimal places comma8.2

DOLLARw.d that contain dollar signs, commas, and decimal places dollar6.2

MMDDYYw. as date values of the form 09/12/97 (MMDDYY8.) or09/12/1997 (MMDDYY10.)mmddyy10.w. rounded to the nearest integer in w spaces 7.

w.d rounded to d decimal places in w spaces 8.2

$w. as character values in w spaces $12.

/*list inut*/

data ampsd;input id flavor & $14. quantity;cards;1 chocolate chip 102 oatmeal unique 123 peanut butter 144 sugar finest 16;proc print;run;

data january_sales; input Item : $12. Amount : comma5.; datalines;Trucks 1,382 Vans 1,235 Sedans 2,391 SportUtility 987; proc print data=january_sales; run;

----------------colon modifier----------------------;

data a1;INPUT @'Breed:' DogBreed :$20.;cards;My dog Sam Breed: Rottweiler Vet Bills: $478;proc print;run;

data a2;INPUT @'Breed:' DogBreed $20.;cards;My dog Sam Breed: Rottweiler Vet Bills: $478;proc print;run;

data a3;

INPUT @'Breed:' DogBreed $;cards;My dog Sam Breed: Rottweiler Vet Bills: $478;proc print;run;

The line pointers, slash (/) and pound-n (#n),When data arrange in sequence.

Ex:

/*USE SLASH(/)*/

DATA A;INPUT FNAME $ LNAME $/ADDRESS $ 1-18/CITY $10. STATE $ ZIP;CARDS;SAC VERMA 1110 RICHMOND ROADBANGALORE KK 560016JAC SHARMA 111I RICHMOND ROADBANGALORE KK 560016;PROC PRINT;RUN;

@@:-it hold the raw data record across the iteration of data step until the line pointer moves past at end of the line.

Ex;

data new;input name $ score @@;datalines;a 2 b 3z 5 v 3c 2 g 7d 7 y 3;proc print;run;

@;- it hold the raw data record in the input buffer until sas reaches the bottom of the data step.

Ex;

data g;input type $ @;IF Type = 'surface' THEN DELETE;INPUT Type$1-8 Name $& 9-36 AMTraffic PMTraffic;cards;freeway 408 3684 3459

surface Martin Luther King Jr. Blvd. 1590 1234surface Broadway 1259 1290surface Rodeo Dr. 1890 2067freeway 608 4583 3860freeway 808 2386 2518surface Lake Shore Dr. 1590 1234surface Pennsylvania Ave. 1259 1290;proc print;run;

data h;*input type $ @;IF Type = 'surface' THEN DELETE;INPUT Type$1-8 Name $& 9-36 AMTraffic PMTraffic;cards;freeway 408 3684 3459surface Martin Luther King Jr. Blvd. 1590 1234surface Broadway 1259 1290surface Rodeo Dr. 1890 2067freeway 608 4583 3860freeway 808 2386 2518surface Lake Shore Dr. 1590 1234surface Pennsylvania Ave. 1259 1290;proc print;run;

Generally: all the option are used in infile statement

Infile ‘path name’ option; (if u r using external rawdata)

Infile cards option; ( if u r using internal raw data)

The DLM= option: delimiter when data value are separated by any recognize symbol like :

(! # $ & * / , @ :) etc.

Dlm=’,’Dlm=’/’Dlm=’*’Dlm=’, / *’ for more then one delimiter.Dlm=’09’x if data separated by tab character.

Ex:

data f;infile cards dlm=',';input name$ age pos;cards;as,12,1 sd,21,2fg,32,3 we,22,4;proc print;run;

data scores;infile cards dlm=':'; input name $ score1 score2 score3; datalines;Joe:11:32:76 Mitchel:13:29:82Susan:14:27:74;

proc print;run;

data scores1;infile cards dlm=':/*#'; input name $ score1-score3; datalines;Joe:11:32/76 Mitchel*13:29:82Susan:14:27#74;proc print;run;

Missover : missing data at end of the record.

Ex:data e;infile cards dlm=','missover;input name$ age num;cards;as,12 sd,21,2fg,32 we,22,4;proc print;run;

Dsd : (delimiter sensitive data)when data separated by comma and some or all value rounded by quotation mark then we use dsd option.

Ex:data abc;infile datalines dsd;input name $ id sex $;datalines;a,1,"m"bf,2,"f"c,3,"m"du,4,"f";proc print;run;

*use of missover dsd ---------------;

data aaa;infile datalines missover dsd ;input name $ id sex $ num;datalines;a,1,"m",54bf,2,"f"c,3,"m",65du,4,"f";proc print;run;

Truncover; some data lines are shorter than others.

Firstobs:firstobs used in infile statement in data step and in option in proc step

Obs:same as firstobs.

Descriptor PortionThe descriptor portion of a SAS data set contains information about the data set, includingthe name of the data setthe date and time that the data set was createdthe number of observationsthe number of variables.

Data PortionThe data portion of a SAS data set is a collection of data values that are arranged in arectangular table.

Variable AttributesIn addition to general information about the data set, the descriptor portion contains informationabout the attributes of each variable in the data set. The attribute information includes thevariable's name, type, length, format, informat, and label.

Label attributes:LABEL variable1='label1'variable2='label2'... ;

Ex:DATA fun;INPUT Id Name $ Height Weight DoB MMDDYY8.;LABEL Id = 'Identification no.' Height = 'Height in inches' Weight = 'Weight in pounds' DoB = 'Date of birth';INFORMAT DoB MMDDYY8.;FORMAT DoB MMDDYY8.;DATALINES;

53 Susie 42 41 07-11-8154 Charlie 46 55 10-26-5455 Calvin 40 35 01-10-8156 Lucy 46 52 01-13-55;proc print;run;

proc print data=fun label;var name height weight dob;LABEL Id = 'Identification no.' Height = 'Height in inches' Weight = 'Weight in pounds' DoB = 'Date of birth';run;

Assigning LibrefsLIBNAME libref 'SAS-data-library';wherelibref is 1 to 8 characters long, begins with a letter or underscore, and contains onlyletters, numbers, or underscores.

Error TypesSo far, the programs that you've seen in this book have been error free, but programming errorsdo occur. SAS can detect several types of errors. The most common aresyntax errors that occur when program statements do not conform to the rules of theSAS languagedata errors that occur when some data values are not appropriate for the SASstatements that are specified in a program.This chapter focuses on identifying and correcting syntax errors.

Syntax CheckingDuring the compilation phase, SAS also scans each statement in the DATA step, looking forsyntax errors. Syntax errors includemissing or misspelled keywordsinvalid variable namesmissing or invalid punctuationinvalid options.

Ex; *--------length--------------------------;

data trn;length designation $12;infile datalines dlm=',';input name $ height weight designation $ ;datalines;sneha,161,54,student

reema,154,49,employeeanil,176,65,mathsteacher;proc print;run;

Chapter-3

Sas function: function perform as a calculation, transformation of an arguments

INPUT(source,informat): character to numeric

Ex:data inp;input salary $;newsal=input(salary,comma6.);cards;32,00025,00012,00020,000;proc print;run;

PUT(source,format): numeric to character

Ex:data puut;input salary;newsal=put(salary,dollar7.);cards;

32000250001200020000

proc print;run;

compress the value:

Ex:

data cp;input phone $1-13;phone=compress(phone,' ().-');cards;(265-467.289);proc print;run;

Truncate function:

INT Function(floor)To return the integer portion of a numeric value

General form, INT function:INT(argument)

ROUND FunctionTo round values after decimal, use the ROUND function.General form, ROUND function:

ROUND (argument, round-off-unit)whereargument is a numeric variable, constant, or expression.round-off-unit is numeric and nonnegative.

Ceil FunctionTo give high value of integer portion

Ceil (argument)

data FICR;input tap;x=floor(tap);z=int(tap);y=ceil(tap);q=round(tap);cards;12.25656;proc print;run;

SAS Date Functions:

MDY date=mdy(mon,day,yr);

TODAY now=today();

DATE now=date();

TIME curtime=time();

You use other functions to extract months, quarters, days, and years from SAS date values.

Function Typical Use ResultDAY day=day(date);

QTR quarter=qtr(date);

WEEKDAY wkday=weekday(date);

MONTH month=month(date);

YEAR yr=year(date);

Ex:data dmy;input date_value;day=day(date_value);month=month(date_value);year=year(date_value);cards;16790603651678916792;proc print;run;

data all;input a ddmmyy10.;day=DAY(a);qtr=qtr(a);today=TODAY();yesterday=today()-1;datetime=datetime();time=time();format today ddmmyy10. yesterday ddmmyy10. datetime datetime19. time time8.;cards;09/03/196010/04/1960

11/05/196012/06/1960;proc print;run;

Modifying Character Values with Functions

SCAN : returns a specified word from a character value.

General form, SCAN function:SCAN(argument,n,delimiters)whereargument specifies the character variable or expression to scann specifies which word to readdelimiters are special characters that must be enclosed in single quotation marks (' ').

Ex:

*--------scan----------;

data avi(drop=fmname);input name $1-15;fmname=scan(name,2,',');fname=left(scan(name,1,','));mname=left(scan(fmname,2,' '));lname=left(scan(fmname,1,' '));fullname=catx(' ',fname,mname,lname);cards;sachin,verma kchetan,patel a;proc print;run;

SUBSTR : extracts a substring or replaces character values.

General form, SUBSTR function:SUBSTR(argument,position,<n>)whereargument specifies the character variable or expression to scan.position is the character position to start from.n specifies the number of characters to extract. If n is omitted, all remaining charactersare included in the substring.

Ex:data abc;input location $1-15;substr(location,5,6)='varma';datalines;goa dharma 787;proc print;run;

TRIM trims trailing blanks from character values.

General form, TRIM function:

TRIM(argument)where argument can be any character expression, such asa character variable: trim(address)another character function: trim(left(id))

Ex:data lis; input @1fname $ @4lname $; fullname=trim(fname)!!lname; datalines;CAT FISH;proc print;run;

CATX:- Concatenates character strings, removes leading and trailing blanks, andinserts separators.

General form, CATX function:CATX(separator,string-1 <,...string-n> )whereseparator specifies the character string that is used as a separator betweenconcatenated stringsstring specifies a SAS character string.

Ex:data lise; input @1fname $ @4lname $; fullname=catx(' ',fname,lname); datalines;CAT FISH;proc print;run;

INDEX searches a character value for a specific string.

General form, INDEX function:INDEX(source,excerpt)wheresource specifies the character variable or expression to searchexcerpt specifies a character string that is enclosed in quotation marks (' ').

FIND searches for a specific substring of characters within a character stringthat you specify.

General form, FIND function:FIND(string,substring<,modifiers><,startpos> )

UPCASE converts all letters in a value to uppercase.

General form, UPCASE function:UPCASE(argument)

Ex:data lse; input @1fname $; fname=upcase(fname); datalines;

sachin;proc print;run;

LOWCASE converts all letters in a value to lowercase.

General form, LOWCASE function:LOWCASE(argument)

Ex:data ise; input @1fname $; fname=lowcase(fname); datalines;CATFISH;proc print;run;

TRANWRD replaces or removes all occurrences of a pattern of characters within acharacter string

General form, TRANWRD function:TRANWRD(source,target,replacement)

Ex:data a;input dessart$1-11;dessart=tranwrd(dessart,'apple','pumpkin');datalines;apple pie;proc print;run;

Using IF-THEN statement:

IF condition THEN action;If action are more then one then we use

IF codtion THENDO Action; Action;

Ex:*--------if then else-----------;

data s; input @1model $9.; if model='maruti' then status='low'; if model='ford' then status='med'; if model='hondacity' then status='hig'; datalines;marutifordhondacity;

proc print;run;

*----------if thendo else----------;

data a; input @1model $9.; if model='maruti' then do;status='low';pos='3';end; else if model='ford' then do;status='med';pos='2';end; else if model='hondacity' then do; status='hig';pos='1';end; datalines;marutifordhondacity;proc print;run;

RETAIN statement: GET BACK THE VALUE IN ANALYSIS

Retain variable;Variable=sum(variable, expression);

Variable+expression;

Ex:data retan(drop=type);length type $1 dep $7;input type $ @;retain id;if type='e' then input id;else input dep $;if dep=' ' then delete;datalines;e: 100d: hindi d: englishd: mathsd: science;proc print;run;

Arrays: arrays used to repetitive calculation in data.

ARRAY statement: ARRAY array-name{dimension} <elements>;

where

array-name specifies the name of the array.dimension describes the number and arrangement of array elements. The defaultdimension is one.

Ex:data songs(drop=i);infile datalines missover; input @1name$ m1 m2 m3 m4 m5 ;array m(5) m1-m5;do i=1 to 5;if m(i)=. then m(i)=3;end;datalines;albay 2 4 7 8 . nethu 1 . 9 6 13 berk 5 7 . 9 13richmo 7 9 5 . 11;

proc print;run;

data son(drop=i); infile datalines missover;input @1name$ k1 k2 k3 k4 k5;array k(5) k1-k5;do i=1 to 5;if k(i)=. then k(i)=3;end;datalines;albay 2 4 7 8 . nethu 1 . 9 6 13berk 5 7 . 9 13richmo 7 9 5 . 11;proc print;run;

DATA songs;INPUT City $ 1-9 Age domk wj hwow simbh kt aomm libm tr filp ttr;ARRAY old(10) domk -- ttr;DO i = 1 TO 10;IF old(i) = 9 THEN old(i) =.;end;cards;Albany 54 4 3 5 9 9 2 1 4 4 9Richmond 33 5 2 4 3 9 2 9 3 3 3Oakland 27 1 3 2 9 9 9 3 4 2 3Richmond 41 4 3 5 5 5 2 9 4 5 5Berkeley 18 3 4 9 1 4 9 3 9 3 2;PROC PRINT ; RUN;

Chapter-4

Program Data VectorAfter the input buffer is created, the program data vector is created. The program data vector isthe area of memory where SAS builds a data set, one observation at a time. Like the term inputbuffer, the term program data vector refers to a logical concept.The program data vector contains two automatic variables that can be used for processing butwhich are not written to the data set as part of an observation._N_ counts the number of times that the DATA step begins to execute._ERROR_ signals the occurrence of an error that is caused by the data during execution.The default value is 0, which means there is no error. When one or more errors occur, thevalue is set to 1.

PROC SORT <DATA=SAS-data-set> out=new sas data set;RUN;

PROC SORT DATA = messy OUT = neat NODUPKEY;BY State DESCENDING City;Run;

General form, basic PROC PRINT step:PROC PRINT <DATA=SAS-data-set>;RUN;

BY variable-list; The BY statement starts a new section in the output for eachnew value of the BY variables and prints the values of the BYvariables at the top of each section. The data must be presortedby the BY variables.

ID variable-list; When you use the ID statement, the observation numbers are notprinted. Instead, the variables in the ID variable list appear onthe left-hand side of the page.

SUM variable-list; The SUM statement prints sums for the variables in the list.

VAR variable-list; The VAR statement specifies which variables to print and theorder. Without a VAR statement, all variables in the SAS data setare printed in the order that they occur in the data set.

PAGEBY BY-variable;where BY-variable identifies a variable that appears in the BY statement in the PROC PRINTstep. PROC PRINT begins printing a new page if the value of any of the variables in the BYstatement changes.

Double-Spacing Listing OutputIf you are generating SAS listing output, one way to control the layout is to double-space it. Todouble-space, specify the DOUBLE option in the PROC PRINT statement.proc print data=clinic.stress double;var resthr maxhr rechr;where tolerance='I';run;

Examples of WHERE StatementsHere are some examples of WHERE statements that use logical operators:where age<=55 and pulse>75;where area='A' or region='S';where ID>1050 and state='NC';When you test for multiple values of the same variable, you specify the variable namein each expression:79where actlevel='LOW' or actlevel='MOD';where fee=124.80 or fee=178.20;You can use the IN operator as a convenient alternative:where actlevel in ('LOW','MOD');where fee in (124.80,178.20);

TITLE and FOOTNOTE Statements:To make your report more meaningful and self-explanatory, you can specify up to 10 titles withprocedure output by using TITLE statements before the PROC step. Likewise, you can specifyup to 10 footnotes by using FOOTNOTE statements before the PROC step.Note Because TITLE and FOOTNOTE statements are global statements, place thembefore the PRINT procedure. Titles and footnotes are assigned as soon asTITLE or FOOTNOTE statements are read; they apply to all subsequent output.General form, TITLE and FOOTNOTE statements:TITLE<n> 'text';FOOTNOTE<n> 'text';

Ex:PROC SORT DATA=SASHELP.CLASS OUT=CLASS1;BY NAME;RUN;PROC PRINT DATA=CLASS1;VAR NAME SEX;RUN;

OPTIONS FIRSTOBS=1 OBS=10;PROC SORT DATA=CLASS OUT=A;BY SEX;RUN;PROC PRINT DATA=A;VAR NAME SEX AGE HEIGHT WEIGHT;

BY SEX;ID SEX;SUM WEIGHT;TITLE1'BI DEPARTMENT';TITLE2 'SAS PROGRAMMER';FOOTNOTE1 'END';FOOTNOTE2 'REPORT';RUN;

/*PAGE BREAK*/

PROC PRINT DATA=A;VAR NAME SEX AGE HEIGHT WEIGHT;BY SEX;PAGEBY SEX;SUM WEIGHT;TITLE1'BI DEPARTMENT';TITLE2 'SAS PROGRAMMER';FOOTNOTE1 'END';FOOTNOTE2 'REPORT';RUN;

PROC PRINT DATA=A;VAR NAME SEX AGE HEIGHT WEIGHT;WHERE AGE=13 AND HEIGHT <59;RUN;

The CONTENTS Procedure:You've learned how to use SAS windows to view the contents of a SAS library or of a SAS file.Alternatively, you can use the CONTENTS procedure to create SAS output that describes eitherof the following:the contents of a librarythe descriptor information for an individual SAS data set.General form, basic PROC CONTENTS step:PROC CONTENTS DATA=libref._ALL_ NODETAILS;RUN;wherelibref is the libref that has been assigned to the SAS library._ALL_ requests a listing of all files in the library. Use a period (.) to append _ALL_ to thelibref.NODETAILS (NODS) suppresses the printing of detailed information about each filewhen you specify _ALL_. You can specify NODS only when you specify _ALL_.

Using the_NULL_ KeywordBecause the goal of your SAS program is to create a raw data file and not a SAS data set, it isinefficient to list a data set name in the DATA statement. Instead, use the keyword _NULL_,which enables you to use the DATA step without actually creating a SAS data set. A SETstatement specifies the SAS data set that you want to read from.data _null_;

PROC FORMAT statement:

PROC FORMAT <options>;

where options includesLIBRARY=libref specifies the libref for a SAS data library that contains a permanentcatalog in which user-defined formats are storedFMTLIB prints the contents of a format catalog.

General form, VALUE statement:VALUE format-namerange1='label1'range2='label2'... ;

Ex;/*Specify a SAS data set from which PROC FORMAT builds informats or formats CNTLIN= *//*Create a SAS data set that stores information about informats or formats CNTLOUT= */data ac;input age sex income color $;datalines;19 1 14000 y45 1 65000 g72 2 35000 b 31 1 44000 y;proc format LIB=SASUSER CTLOUT=SASUSER.FMT;value gender 1='male'2='female';value agegroup 13-<20='teen'20-<60='adult'65-high='senior';value $col 'y'='moon white''b'='sky blue' 'g'='rain cloudy gray';RUN;

PROC FORMAT CNTLIN=SASUSER.FMT;RUN;

proc print data=ac;format sex gender. age agegroup. color $col. income dollar8.;id color;run;

/*TO SEE FORMAT*/PROC FORMAT LIB=SASUSER FMTLIB;SELECT AGEGROUP;RUN;

The CONTENTS Procedure:You've learned how to use SAS windows to view the contents of a SAS library or of a SAS file.Alternatively, you can use the CONTENTS procedure to create SAS output that describes eitherof the following:the contents of a librarythe descriptor information for an individual SAS data set.General form, basic PROC CONTENTS step:PROC CONTENTS DATA=libref._ALL_ NODETAILS;RUN;wherelibref is the libref that has been assigned to the SAS library._ALL_ requests a listing of all files in the library. Use a period (.) to append _ALL_ to the

libref.NODETAILS (NODS) suppresses the printing of detailed information about each filewhen you specify _ALL_. You can specify NODS only when you specify _ALL_.

EX:proc contents data=SASHELP.CLASS;run;

proc contents data=sasuser._all_ nodetails;run;proc contents data=sasuser._all_ nods;run;

The MEANS procedure

The MEANS procedure provides simple statistics on numeric variables. Look at some simple statistics. Statistics such as the mean value, standard deviation, and minimum and maximum values give you a feel for your data.

PROC MEANS data= SAS-data-set> options;

Options;MAX the maximum valueMIN the minimum valueMEAN the meanMEDIAN the medianN number of non-missing valuesNMISS number of missing valuesRANGE the rangeSTDDEV the standard deviationSUM the sum

Here are some of the optional statements you may want to use in means procedure :

BY variable-list; The BY statement performs separate analyses for eachlevel of the variables in the list.1 The data must first besorted in the same order as the variable-list. (You can usePROC SORT to do this.)

CLASS variable-list; The CLASS statement also performs separate analysesfor each level of the variables in the list, 1 but its outputis more compact than with the BY statement, and thedata do not have to be sorted first.

VAR variable-list; The VAR statement specifies which numeric variables touse in the analysis. If it is absent then SAS uses allnumeric variables.

The OUTPUT statement:-For summary statistics

OUTPUT OUT = data-set output-statistic-list;

Ex:-

PROC MEANS DATA = zoo NOPRINT;VAR Lions Tigers Bears;OUTPUT OUT = zoosum MEAN(Lions Bears) = LionWeight BearWeight;RUN;

Ex:

DATA VIRUS;INPUT DILUTION $ COMPOUND $ TIME @@; IF DILUTION='A' THEN DL=1; ELSE IF DILUTION='B' THEN DL=2; ELSE IF DILUTION='C' THEN DL=4; CARDS; A PA 87 A PA 90 A PM 82 A PM 71 A UN 72 A UN 77 B PA 79 B PA 80 B PM 73 B PM 72 B UN 70 B UN 66 C PA 77 C PA 81 C PM 72 C PM 68 C UN 62 C UN 61 ;

PROC MEANS DATA=VIRUS n mean min max nmiss median std ;run;

PROC SORT; BY COMPOUND;run;

PROC MEANS DATA=VIRUS; VAR TIME; CLASS COMPOUND; OUTPUT OUT=OUTA MEAN=M STD=S N=COUNT; PROC PRINT; RUN;

PROC SORT; BY COMPOUND;run;

PROC SUMMARY DATA=VIRUS PRINT; VAR TIME; CLASS COMPOUND; OUTPUT OUT=OUTB MEAN=M STD=S N=COUNT; PROC PRINT; RUN;

Counting Your Data with PROC FREQ:

It is analyze the variable compute statistics and compute association between the variable thorough freq.Ex:-

PROC FREQ data=<sas data set>;

TABLES variable-combinations;

TABLES YearsEducation;

TABLES Sex * YearsEducation;

LIST prints cross-tabulations in list format rather than gridMISSING includes missing values in frequency statisticsNOCOL suppresses printing of column percentages in cross-tabulationsNOROW suppresses printing of row percentages in cross-tabulationsOUT = data-set writes a data set containing frequencies

Ex:proc freq data=SASHELP.CLASS;tables SEX*WEIGHT*height;run;

proc freq data=SASHELP.CLASS;tables SEX*WEIGHT*height/LIST;run;

proc freq data=SASHELP.CLASS;tables SEX*WEIGHT/LIST NOROW NOCOL NOCUM NOPERCENT;run;

Producing Tabular Reports with PROC TABULATE

It displays descriptive statistics in tabulate format using some or all of the variable in data set.

PROC TABULATE;VAR analysis-variable-list;CLASS classification-variable-list;TABLE page-dimension, row-dimension, column-dimension;

Operator actionComma go to the new tableBlank concatenate tableAsterisk nested group

Keywords In addition to variable names, each dimension can contain keywords. These are afew of the values TABULATE can compute.ALL adds a row, column, or page showing the totalMAX highest valueMIN lowest valueMEAN the arithmetic meanMEDIAN the medianN number of non-missing valuesNMISS number of missing valuesP90 the 90th percentilePCTN the percentage of observations for that groupPCTSUM the percentage of a total sum represented by that groupSTDDEV the standard deviationSUM the sum

FORMAT= option To change the format of all the data cells in your table, use the

FORMAT= option in your PROC statement. For example, if you needed the numbers in yourtable to have commas and no decimal places, you could use this PROC statementPROC TABULATE FORMAT=COMMA10.0;

BOX= and MISSTEXT= options

TABLE Region, MEAN*Sales / BOX='Mean Sales by Region' MISSTEXT='No Sales';

Ex:data boats;input name$1-7 port$9-15 locomotion$17-22 price 23-25 type$26-29;datalines;silent maalea sail 75 schamerica maalea sail 32 yacaloha lahaina sail 62 catocean maalea power 52 catanuenue maalae sail 47 schhana maalae power 28 yac;

proc tabulate data=boats;class locomotion type;var price;table locomotion price type;run;

proc tabulate data=boats;class locomotion type;var price;table locomotion all price type all;run; /*little bit best*/proc tabulate data=boats;class locomotion type;var price;table locomotion price, type all/box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

proc tabulate data=boats;class locomotion type;var price;table locomotion, price, type all/box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

proc tabulate data=boats;class locomotion type;var price;table locomotion all, mean*price*type all /box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

proc tabulate data=boats;class locomotion type;var price;table locomotion all, mean*price*(type all) /box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

/*best code*/proc tabulate data=boats;class locomotion type;var price;table locomotion all mean*price*(type all )/box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

proc tabulate data=boats;class locomotion type;var price;table locomotion mean*price*(type all )/box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

proc tabulate data=boats format=dollar9.2;class locomotion type;var price;table locomotion* mean*price*(type all )/box='full day excursion' misstext='none' ;title 'mean price by locomotion and type';run;

Producing Simple Output with PROC REPORT

To create list and summary report we use report procedure.In report procedure we can sort datawe can modify data.Format data.Group reportList report.We can change label.So this procedure is integration of print sort tabulate and means.

PROC REPORT NOWINDOWS;COLUMN variable-list;

PROC REPORT NOWINDOWS HEADLINE HEADSKIP;HEADLINE draws a line under the column headers while HEADSKIP puts a blank line beneaththe column headers.

DEFINE variable / options ’column-header’; to modify your variable attribute .

Define option:

ACROSS creates a column for each unique value of the variable.ANALYSIS calculates statistics for the variable. This is the default usage for numericvariables, and the default statistic is sum.DISPLAY creates one row for each observation in the data set. This is the default usagefor character variables.GROUP creates a row for each unique value of the variable.ORDER creates one row for each observation with rows arranged according to thevalues of the order variable.

BREAK location variable / options;(SUBTOTAL)RBREAK location / options;(GRANT TOTAL)

where location has two possible values BEFORE or AFTER

Break rbreak options

OL draws a line over the breakPAGE starts a new pageSKIP inserts a blank lineSUMMARIZE inserts sums of numeric variablesUL draws a line under the break

PROC REPORT compute options:MAX highest valueMIN lowest valueMEAN the arithmetic meanMEDIAN the medianN number of non-missing valuesNMISS number of missing valuesP90 the 90th percentilePCTN the percentage of observations for that groupPCTSUM the percentage of a total sum represented by that groupSTD the standard deviationSUM the sum

Ex:

data budget; input QTR @8 DEPT $10. @22 ACCOUNT $8. BUDGET ACTUAL; format actual budget dollar11.2 dept $10.; cards; 1 Staff fulltime 130000.00 127642.68 2 Staff fulltime 165000.00 166345.75 1 Staff parttime 40000.00 43850.12 2 Staff parttime 60000.00 56018.96 1 Equipment lease 40000.00 40000.00 2 Equipment lease 40000.00 40000.00 1 Equipment purchase 40000.00 48282.38 2 Equipment purchase 20000.00 17769.15 1 Equipment tape 8000.00 6829.42 2 Equipment tape 12000.00 11426.73 1 Equipment sets 7500.00 8342.68 2 Equipment sets 7500.00 8079.62 1 Equipment maint 10000.00 7542.13 2 Equipment maint 12000.00 10675.29

1 Equipment rental 4000.00 3998.87 2 Equipment rental 6000.00 5482.94 1 Facilities rent 24000.00 24000.00 2 Facilities rent 24000.00 24000.00 1 Facilities utils 5000.00 4223.29 2 Facilities utils 3500.00 3444.81 1 Facilities supplies 2750.00 2216.55 2 Facilities supplies 2750.00 2742.48 1 Travel leases 3500.00 3045.15 2 Travel leases 4500.00 3889.65 1 Travel gas 800.00 537.26 2 Travel gas 1200.00 984.93 1 Other advert 30000.00 32476.98 2 Other advert 30000.00 37325.64 1 Other talent 13500.00 12986.73 2 Other talent 19500.00 18424.64 1 Other musicfee 3000.00 2550.50 2 Other musicfee 5000.00 4875.95 ;

data vip;input name$1-17 type$19-20 region$22-25 pos 28-29 df ;datalines;diposaur nm west 2 3ellis island nm east 3 5everglades np west 2 7statue of liberty nm east 1 9yosemite np west 3 2hawaii volcanoes np west 2 7;

proc report data=vip nowindows headline;column name type region pos df;define type/width=4 'type';define region/width=6 'region';define pos/ width=11 'position';run;

proc report data=vip nowindows headline;column name type region pos df;define type/group width=4 spacing=5 'type' right;define region/group width=6 spacing=5 'region/wise' center;define pos /width=11 'position';run;

proc report data=vip nowindows headline split='*';column name type region pos df;define type/order width=4 spacing=5 'type' right;define region/order width=6 spacing=5 'region*wise' center;define pos /width=11 'position';run;

proc report data=vip headline nowindows MISSING;column name type region pos;define type /order width=4;define region/order width=6;

RBREAK AFTER/SUMMARIZE DOL DUL SKIP;run;

proc report data=vip headline nowindows MISSING;column name type region pos;define type /order width=4;define region/order width=6;*RBREAK AFTER/SUMMARIZE DOL DUL SKIP;run;

proc report data=work.budget1 headline nowindows headskip; column dept (budget actual),sum ; define dept/group ; run;

proc report data=vip nowindows headline MISSING;column region type,(pos df);define type /ACROSS;define region /group;run;

DATA natparks;infile cards missover;length name $19 type $2 Region $4;INPUT Name $ Type $ Region $ Museums Camping;cards;Diposaur NM West 2 6EllisIsland NM East 1 0Everglades NP East 5 2GrandCanyon NP West 5 3GreatSmokyMountains NP East 3 10HawaiiVolcanoes NP West 2 2LavaBeds NM West 1 1StatueofLiberty NM East 1 0TheodoreRoosevelt NP . 2 2Yellowstone NP West 9 11Yosemite NP West 2 13;

PROC REPORT DATA = natparks NOWINDOWS HEADLINE;COLUMN Region Type N (Museums Camping),MEAN;DEFINE Region / GROUP;DEFINE Type / GROUP;TITLE 'Statistics with Two Group Variables';RUN;

PROC REPORT DATA = natparks NOWINDOWS HEADLINE;COLUMN Region Type (Museums Camping),mean;*DEFINE Region / GROUP;*DEFINE Type / GROUP;TITLE 'Statistics with Two Group Variables';RUN;

PROC REPORT DATA = natparks NOWINDOWS HEADLINE;COLUMN Region N Type,(Museums Camping),MEAN;DEFINE Region / group;DEFINE Type / ACROSS;

TITLE 'Statistics with a Group and Across Variable';RUN; proc report data=work.budget headline nowindows headskip out=balance; title1 'Report of Departments'; title2 'by Quarter'; column qtr dept account budget actual balance; define qtr/order format=1. width=7 'Quarter'; define dept/order format=$10. width=10 'Department'; define account/order format=$8. width=8 'Account'; define budget/display format=dollar11.2 width=11 'Amount/Budgeted'; define actual/display format=dollar11.2 width=11 'Amount/Spent'; define balance/computed format=dollar11.2 width=11 'Balance'; compute balance; balance=budget-actual; endcomp;break after qtr/page; run;

data balance(drop=_break_);set balance;if dep=’’ then delete;run;

proc report data=balance headline nowindows headskip;column qtr dept account budget actual balance;define qtr/order format=1. width=7 'Quarter';define dept/order format=$10. width=10 'Department';define account/order format=$8. width=8 'Account';break after qtr/suppress summarize dol dul skip;rbreak after / summarize dol dul skip;run;

CHAPTER-5

(OUT PUT DELIVERY SYSTEM)

Output Delivery System(ODS): Output Delivery System (ODS) which determines where the output should go and what it should look like when it gets there.

Some important destination:LISTING standard SAS outputOUTPUT SAS output data setHTML Hypertext Markup LanguageRTF Rich Text FormatPDF Portable Document FormatMARKUP markup languages including XML

The ODS TRACE statement The ODS TRACE statement tells SAS to print information about output objects in your SAS log. There are two ODS TRACE statements: one to turn on the trace, and one to turn it off. Here is how to use these statements in a program:

ODS TRACE ON;the PROC steps you want to trace go hereRUN;ODS TRACE OFF;

EX:DATA ot;INFILE datalines DSD;INPUT Name :$15. Color $ Days Weight;datalines;Big Zac, red, 80, 5Delicious, red, 80, 3Dinner Plate, red, 90, 2Goliath, red, 85, 1.5Mega Tom, red, 80, 2Big Rainbow, yellow, 90, 1.5Pineapple, yellow, 85, 2;

ODS TRACE ON;PROC MEANS DATA = ot;BY Color;RUN;ODS TRACE OFF;

PROC TABULATE DATA = OT1;CLASS Color;VAR Days Weight;TABLE Color ALL, (Days Weight) * MEAN;TITLE 'Standard TABULATE Output';ODS OUTPUT Table = tabout;RUN;PROC PRINT DATA = tabout;TITLE 'OUTPUT SAS Data Set from TABULATE';RUN;

Using ODS Statements to Create HTML Output:

ODS statement:

ODS HTML BODY = 'body-filename.html' options;

CONTENTS= The contents file is a table of contents with links to the body file. Thecontents file will list each part of your output, and when you click on an

entry in the table of contents, that part of the output will appear.

PAGE= The page file is similar to the contents file, except instead of labeling thedifferent parts of the output, it lists the output by page number.

FRAME= The frame file allows you to view the body file and the contents or thepage file at the same time in different areas, or frames, of the browserwindow. If you do not want either the contents or the page file, then youdon’t need to create a frame file.

STYLE= This option allows you to specify a style template. The default style isnamed DEFAULT.

EX:

ODS HTML BODY = 'c:\MyHTMLfiles\MarineBody.html'CONTENTS = 'c:\MyHTMLfiles\MarineTOC.html'PAGE = 'c:\MyHTMLfiles\MarinePage.html'FRAME = 'c:\MyHTMLfiles\MarineFrame.html';DATA marine;INPUT Name $ Family $ Length @@;CARDS;beluga whale 15 dwarf shark .5 sperm whale 60basking shark 30 humpback whale 50 whale shark 40gray whale 50 blue whale 100 killer whale 30mako shark 12;PROC MEANS DATA = marine;CLASS Family;TITLE 'Whales and Sharks';PROC PRINT DATA = marine;RUN;ODS HTML CLOSE;

Using ODS Statements to Create RTF Output:

ODS statement:

ODS RTF FILE = 'filename.rtf' options;EX:

ODS RTF FILE = 'c:\MyHTMLfiles\Marine.rtf' BODYTITLE;PROC MEANS DATA = marine;CLASS Family;TITLE 'Whales and Sharks';PROC PRINT DATA = marine;RUN;ODS RTF CLOSE;

Using ODS Statements to Create PDF Output:

ODS statement:

ODS PDF FILE = 'filename.pdf' options;

EX:

ODS PDF FILE = 'c:\MyHTMLfiles\Marine.pdf';PROC MEANS DATA = marine;CLASS Family;TITLE 'Whales and Sharks';PROC PRINT DATA = marine;RUN;ODS PDF CLOSE;

Customizing PROC PRINT Output with the STYLE= Option:

PROC PRINT STYLE(location-list) = {style-attribute = value};

PROC PRINT STYLE(DATA) = {BACKGROUND = pink};

VAR variable-list / STYLE(location-list) = {style-attribute = value};

EX:

ODS HTML FILE='c:\MyHTMLfiles\skating.htm';DATA skating;INFILE cards DSD MISSOVER;INPUT Year Name :$20. Country $Time $ Record $;cards;1988,Yvonne van Gennip,NED,7:14.13,WR1992,Gunda Niemann,GER,7:31.571994,Claudia Pechstein,GER,7:14.371998,Claudia Pechstein,GER,6:59.61,WR2002,Claudia Pechstein,GER,6:46.91,WR;

PROC PRINT DATA=skating STYLE(DATA)={BACKGROUND=white};TITLE 'Women''s 5000 Meter Speed Skating';ID Year;RUN;ODS HTML CLOSE;

ODS HTML FILE='c:\MyHTMLfiles\skating3.htm';PROC PRINT DATA=skatingSTYLE(DATA)={BACKGROUND=white};TITLE 'Women''s 5000 Meter Speed Skating';VAR Name Country Time;VAR Record/STYLE(DATA)={FONT_STYLE=italic FONT_WEIGHT=bold};ID Year;RUN;ODS HTML CLOSE;

Customizing PROC REPORT Output with the STYLE= Option:

PROC REPORT STYLE(location-list) = {style-attribute = value};

PROC REPORT DATA = mysales STYLE(HEADER) = {BACKGROUND = green};

DEFINE Month / GROUP STYLE(HEADER COLUMN) = {BACKGROUND = blue};

BREAK AFTER Month / SUMMARIZE STYLE(SUMMARY) = {BACKGROUND = red};RBREAK AFTER / SUMMARIZE STYLE(SUMMARY) = {BACKGROUND = orange};

EX:

DATA skat;INFILE cards DSD;INPUT Name :$20. Country $Year NumGold @@;datalines;Lydia Skoblikova, URS, 1960, 2, Lydia Skoblikova, URS, 1964, 4Karin Enke, GDR, 1980, 1, Karin Enke, GDR, 1984, 2Christa Rothenburger, GDR, 1984, 1, Christa Rothenburger, GDR, 1988, 1Bonnie Blair, USA, 1988, 1, Bonnie Blair, USA, 1992, 2Gunda Nieman, GDR, 1992, 2, Bonnie Blair, USA, 1994, 2Claudia Pechstein, GER, 1994, 1, Gunda Nieman, GDR, 1998, 1Claudia Pechstein, GER, 1998, 1, Catriona LeMay, CAN, 1998, 1Claudia Pechstein, GER, 2002, 2, Catriona LeMay, CAN, 2002, 1;

ODS HTML FILE='c:\MyHTMLfiles\skat.html';PROC REPORT DATA = skat nowindows;COLUMN Name Country NumGold;DEFINE Name / GROUP;DEFINE Country / GROUP;TITLE 'Olympic Women''s ''Speed Skating';RUN;ODS HTML CLOSE;

* STYLE= option in PROC statement;ODS HTML FILE='c:\MyHTMLfiles\skat2.htm';PROC REPORT DATA = skat NOWINDOWSSTYLE(COLUMN) ={BACKGROUND = white JUST = center};COLUMN Name Country NumGold, SUM;DEFINE Name / GROUP;DEFINE Country / GROUP;TITLE 'Olympic Women''s ''Speed Skating';RUN;ODS HTML CLOSE;

* STYLE= option in DEFINE statement;ODS HTML FILE='c:\MyHTMLfiles\skat3.htm';PROC REPORT DATA = skat NOWINDOWS;COLUMN Name Country NumGold, SUM;DEFINE Name / GROUPSTYLE(COLUMN) ={BACKGROUND = white JUST = center};DEFINE Country / GROUP;TITLE 'Olympic Women''s ''Speed Skating';RUN;ODS HTML CLOSE;

Customizing PROC TABULATE Output with the STYLE=Option

PROC TABULATE DATA = mysales STYLE = {BACKGROUND = yellow};

TABLE City, Month ALL*{STYLE = {BACKGROUND = red}};

CLASSLEV Month / STYLE = {FOREGROUND = green};

FOR USING DEFINE FORMAT:

PROC FORMAT;VALUE posnegLOW -< 0 = ‘red’0-HIGH = ‘black’;

VAR Balance / STYLE = {FOREGROUND = posneg.};

EX:

ODS HTML FILE='c:\MyHTMLfiles\results.html';DATA results;INFILE cards DSD;INPUT Place Name :$20.Country :$15. Time;cards;1,Jochem Uytdehaage, Netherlands,374.662,Derek Parra, United States,377.983,Jens Boden, Germany,381.734,Dmitry Shepel, Russia,381.855,KC Boutiette, United States,382.97;PROC PRINT DATA=results;ID Place;TITLE 'Men''s 5000m Speed Skating';TITLE2 '2002 Olympic Results';RUN;ODS HTML CLOSE;

ODS HTML FILE='c:\MyHTMLfiles\results1.html';PROC FORMAT;VALUE rec 0 -< 378.72 ='red'378.72 -< 382.20 = 'orange'382.20 - HIGH = 'white';PROC PRINT DATA=results;ID Place;VAR Name Country;VAR Time/STYLE={BACKGROUND=rec.};TITLE 'Men''s 5000m Speed Skating';TITLE2 '2002 Olympic Results';RUN;ODS HTML CLOSE;

CHAPTER-6

(Modifying and Combining SAS_ Data Sets)

SET: The SET statement with one SAS data set allows you to read and modify thedata.

With two or more data sets, in addition to reading and modifying thedata, the SET statement concatenates the data.

Merge : When you want to match observations from one data setwith observations from another, use the MERGE statementin the DATA step.

By : if you are using by statement in set or merge. so first your data should be sorted in order.

data a;input name$ pos;cards;a 1b 2 c 3d 4;

data b;input name$ rank;cards;a 2 c 3d 5;

data z;merge a b;run;

data z1;merge a b;by name;run;

data d;set a b;run;

data d1;set a b;by name;run;

data f;set a;set b;by name;run;

data one;input id A B;cards;10 1 220 3 430 5 6;

data two;input id C;cards;10 030 1;

data x1y1 x1y0 x0y1;merge one(in=x) two(in=y);by id;if x=1 and y=1 then output x1y1; if x=1 and y=0 then output x1y0;if x=0 and y=1 then output x0y1;run;

UPDATE: the speciality of update is that, the Missing values in the transaction data set do not overwrite existing values in the master data set.

Data Master;infile cards dlm=' ';input ID NAME$ TEAM$ WEIGHT;cards;

1114 sally blue 125 1441 sue green 145 1750 joey red 189 1994 mark yellow 1652304 joe red 170;

Data transaction;infile cards dlm=' ';input ID NAME$ TEAM$ WEIGHT;cards; 1114 sally blue 1191994 mark yellow 1742304 joe red 170;

Data up;update master tran;by id;

run;

FIRST.variable and LAST.variable: Other automatic variables are available only inspecial circumstances. The FIRST.variable and LAST.variable automatic variables are availablewhen you are using a BY statement in a DATA step. The FIRST.variable will have a value of 1when SAS is processing an observation with the first occurrence of a new value for that variableand a value of 0 for the other observations. The LAST.variable will have a value of 1 for anobservation with the last occurrence of a value for that variable and the value 0 for the otherobservations.

*--------------------FIRST AND LAST--------;

DATA A;INPUT REGION $ SALE;CARDS;NORTH 100NORTH 110NORTH 120 NORTH 125NORTH 130NORTH 140SOUTH 100SOUTH 110SOUTH 120 SOUTH 123SOUTH 138SOUTH 142EAST 105 EAST 115EAST 119EAST 127 EAST 130EAST 136WEST 102WEST 119WEST 126WEST 131WEST 139WEST 143WEST 149;

PROC SORT DATA=A OUT=B;BY REGION DESCENDING SALE;RUN;DATA C;SET B;BY REGION;RETAIN R;IF FIRST.REGION THEN R=0;R=R+1;IF R<=5;

DROP R;RUN;

TRANSPOSE: the transposes procedure, turning observations into variables or variables into observations. In most cases, to convert observations into variables, you can use the following statements

DATA E;INPUT SUBJECT VISIT SBP;CARDS;101 1 160101 3 140101 4 150202 1 141202 2 151202 3 161;

:

PROC TRANSPOSE DATA = old-data-set OUT = new-data-set;BY variable-list;ID variable;VAR variable-list;

PROC TRANSPOSE DATA=E OUT=F PREFIX=VISIT;BY SUBJECT;RUN;

*--------------do loop---------------;

data invest1;do year=2000 to 2010;capital+5000;capital+(capital*.075);end;run;proc print ;run;

data invest2;do year=2000 to 2010;capital+5000;capital+(capital*.075);output;end;run;

proc print ;run;

data invest3;capital=0;do year=1 to 10;capital+5000;do qtr=1 to 4;capital+(capital*.075);end;output;end;output;run;proc print ;run;

data invest4;capital=0;do year=1 to 10;capital+5000;do qtr=1 to 4;capital+(capital*.075);end;end;run;proc print ;run;

data invest5;do year=1 to 25 until(capital>250000);capital+5000;capital+(capital*.075);output;end;run;proc print ;run;

data invest6;do year=1 to 25 while(capital<250000);capital+5000;capital+(capital*.075);output;end;run;proc print ;run;

SAS/GRAPH

DATA auto ; INPUT make $ mpg rep78 weight foreign ;CARDS;AMC 22 3 2930 0AMC 17 3 3350 0AMC 22 . 2640 0

Audi 17 5 2830 1Audi 23 3 2070 1BMW 25 4 2650 1Buick 20 3 3250 0Buick 15 4 4080 0Buick 18 3 3670 0Buick 26 . 2230 0Buick 20 3 3280 0Buick 16 3 3880 0Buick 19 3 3400 0Cad. 14 3 4330 0Cad. 14 2 3900 0Cad. 21 3 4290 0Chev. 29 3 2110 0Chev. 16 4 3690 0Chev. 22 3 3180 0Chev. 22 2 3220 0Chev. 24 2 2750 0Chev. 19 3 3430 0Datsun 23 4 2370 1Datsun 35 5 2020 1Datsun 24 4 2280 1Datsun 21 4 2750 1;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC CHART DATA=auto;VBAR mpg make;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC CHART DATA=auto;hBAR mpg make;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC GCHART DATA=auto; VBAR mpg;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC GCHART DATA=auto; HBAR mpg;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC GCHART DATA=auto; VBAR mpg/DISCRETE;RUN;

TITLE 'Simple Vertical Bar Chart ';PROC GCHART DATA=auto; HBAR mpg/DISCRETE;RUN;

TITLE 'Bar Chart - Control Number of Bins';PROC GCHART; VBAR mpg/LEVELS=7;RUN;

TITLE 'Pie Chart with Discrete';PROC GCHART DATA=auto; PIE rep78/ DISCRETE VALUE=INSIDE PERCENT=INSIDE SLICE=OUTSIDE;RUN;

TITLE 'Scatterplot - Two Variables';PROC PLOT DATA=auto; PLOT mpg*weight=’*’ ;RUN;

TITLE 'Scatterplot - Two Variables';PROC GPLOT DATA=auto; PLOT mpg*weight ;RUN;

SYMBOL1 V=circle C=black I=none; SYMBOL2 V=star C=red I=none;

TITLE 'Scatterplot - Different Symbols'; PROC GPLOT DATA=auto; PLOT mpg*weight=foreign; RUN; QUIT;

sumvar specifies the numeric variable for sum or mean calculations

proc gchart data=s.d.s;vbar jobcode salary/sumvar=salary type=mean;run;

proc gchart data=s.d.s;pie jobcode salary/sumvar=salary type=sum;format salary dollar8.;run;

proc gplot data=sashelp.class;plot Age*Height;

symbol c=red v=square;run;quit;

proc gplot data=sashelp.class;plot name*Height;symbol c=red v=square;run;quit;

proc gplot data=sashelp.class;plot name*Height/ctext=blue;symbol c=red v=star i=needle ;run;quit;

*(i=join needle spline);*(v=symbol w=width c=color);

goptions reset=global;*to specify graphic device;proc gplot data=sashelp.class;plot (Age Height Weight)* Name/overlay haxis=axis1 vaxis=axis2 caxis=red ;axis1 order=1 to 8 by 1 ;axis2 order=1 to 210 by 30;symbol1 color=green interpol=spline width=2 value=triangle height=3 pointlabel=(h=1.5 '#Age');symbol2 color=blue interpol=spline width=2 value=circle height=3 pointlabel=(h=1.5 '#Height'); symbol3 color=red interpol=spline width=2 value=square height=3 pointlabel=(h=1.5 '#Weight'); run;quit;

CH-7(macro)

The macro processor When you submit a standard SAS program, SAS compiles and thenimmediately executes it. But when you write a macro, there is an additional step. Before SAScan compile and execute your program, SAS must pass your macro statements to the macroprocessor which “resolves” your macros, generating standard SAS code

EX: Macro MACRO Standard statements Processor SAS statements

Macros and macro variables SAS macro code consists of two basic parts: macros and

macro variables. The names of macro variables are prefixed with an ampersand (&) while thenames of macros are prefixed with a percent sign (%).

Local versus global Macro variables can have two kinds of “scope”_either local or global.Generally, a macro variable is local if it is defined inside a macro. A macro variable is generallyglobal if it is defined in “open code” which is everything outside a macro. You can use a globalmacro variable anywhere in your program, but you can use a local macro variable only inside itsown macro. 2 If you keep this in mind as you write your programs, you will avoid two commonerrors: trying to use a local macro variable outside its macro and accidentally creating local andglobal macro variables with the same name.

Turning on the macro processor Before you can use macros you must have the MACROsystem option turned on. This option is usually turned on by default, but may be turned off,especially on mainframes, because SAS runs slightly faster when it doesn’t have to bother withchecking for macros. If you are not sure whether the MACRO option is on, you can find out bysubmitting these statements:

PROC OPTIONS OPTION = MACRO; RUN;

Ex:

OPTIONS MPRINT SYMBOLGEN MLOGIC;

%let a=june;%let b=july;%let c=&a&b;%let d=may&a;%let e=&a.&b;%let F=&a..&b;%put &a &b &c &d &e &f;

%let a=var name sex age height weight;%let b=sashelp.class;%put class;%macro class;proc print data=&b;&a;run;%mend class;

%classrun;

%LET A= SET SASHELP.CLASS;DATA SD;&A;RUN;

%LET ftyp = Ginger;DATA flowersales;INPUT CustomerID $ @6 SaleDate MMDDYY10.@17 Variety $ Quantity;

IF Variety ="&ftyp";DATALINES;240W 02-07-2003 Ginger 120240W 02-07-2003 Protea 180356W 02-08-2003 Heliconia 60356W 02-08-2003 Anthurium 300188R 02-11-2003 Ginger 24188R 02-11-2003 Anthurium 24240W 02-12-2003 Heliconia 48240W 02-12-2003 Protea 48356W 02-12-2003 Ginger 240;

PROC PRINT DATA = flowersales;FORMAT SaleDate WORDDATE18.;TITLE "Sales of &ftyp";RUN;

Avoiding macro errors There’s no question about it, macros can make your head hurt. Youcan avoid the macro migraine by developing your program in a piecewise fashion. First, write yourprogram in standard SAS code. Then, when it’s bug-free, convert it to macro logic adding onefeature at a time. This modular approach to programming is always a good idea, but it’s criticalwith macros.

Creating Modular Code with Macros:

%MACRO macro-name;macro-text%MEND macro-name;

Ex:DATA flowersale;INPUT CustomerID $ @6 SaleDate MMDDYY10.@17 Variety $ Quantity;DATALINES;240W 02-07-2003 Ginger 120240W 02-07-2003 Protea 180356W 02-08-2003 Heliconia 60356W 02-08-2003 Anthurium 300188R 02-11-2003 Ginger 24188R 02-11-2003 Anthurium 24240W 02-12-2003 Heliconia 48240W 02-12-2003 Protea 48356W 02-12-2003 Ginger 240;

%MACRO sample;PROC SORT DATA = flowersale;BY DESCENDING Quantity;PROC PRINT DATA = flowersale (OBS = 5);FORMAT SaleDate WORDDATE18.;TITLE 'Five Largest Sales';%MEND sample;

%sampleRUN;

Adding Parameters to Macros:MACRO statement. Here is one of the possible forms of the parameter-list.%MACRO macro-name (parameter-1= ,parameter-2= , . . . parameter-n=);macro-text%MEND macro-name;

Writing Macros with Conditional Logic:

%IF condition %THEN action;%ELSE %IF condition %THEN action;%ELSE action;%IF condition %THEN %DO;SAS statements%END;

%MACRO select(customer=,sortvar=);PROC SORT DATA = flowersale OUT = salesout;BY &sortvar;WHERE CustomerID ="&customer";PROC PRINT DATA = salesout;FORMAT SaleDate WORDDATE18.;TITLE1 "Orders for Customer Number &customer";TITLE2 "Sorted by &sortvar";%MEND select;%select(customer = 356W, sortvar = Quantity)%select(customer = 240W, sortvar = Variety)RUN;

Automatic macro variables Every time you invoke SAS, the macro processor automaticallycreates certain macro variables. You can use these variables in your programs. The most commonautomatic macro variables areVariable name Example Description&SYSDATE 29MAY02 the character value of the date that job or session began&SYSDAY Wednesday the day of the week that job or session began.

Ex:

%MACRO dailyreports;%IF &SYSDAY = tuesday %THEN %DO;PROC PRINT DATA = flowersale;FORMAT SaleDate WORDDATE18.;TITLE 'Monday Report: Current Flower Sales';%END;%MEND dailyreports;

%dailyreportsRUN;

data ap;input sale;datalines;100011001200

130014001500;

%macro settax(taxrate=); %let taxrate = %upcase(&taxrate); %if &taxrate = 'CHANGE' %then %do; data new; set WORK.AP; if sale > 1000 then tax = .05; else tax = .08; run; proc print data=new; run;

%end; %else %if &taxrate = 'SAME' %then %do; data old; set WORK.AP; tax = .03; run; proc print data=old; run; %end;%mend settax;

%SETTAX(taxrate='CHANGE')

%SETTAX(taxrate='SAME')

CALL SYMPUT:

CALL SYMPUT takes a value from a DATA step and assigns it to a macro variable. You can thenuse this macro variable in later steps. To assign a value to a single macro variable, you use CALLSYMPUT with this general form:CALL SYMPUT(”macro-variable-name”,value);

DATA flowersales;length variety $9;INPUT CustomerID$ SaleDate MMDDYY10. Variety$ Quantity;format saledate ddmmyy10.;cards;240W 02-07-2003 Ginger 120240W 02-07-2003 Protea 180356W 02-08-2003 Heliconia 60356W 02-08-2003 Anthurium 300188R 02-11-2003 Ginger 24188R 02-11-2003 Anthurium 24240W 02-12-2003 Heliconia 48240W 02-12-2003 Protea 48356W 02-12-2003 Ginger 240;

PROC SORT DATA = flowersales;BY DESCENDING Quantity;run;

DATA flowersales2;SET flowersales;IF customerID='356W' THEN CALL SYMPUT("id",customerID);run;

PROC PRINT DATA = flowersales2;WHERE CustomerID = "&id";FORMAT SaleDate WORDDATE18.;TITLE "Customer &id Had the Single Largest Order";RUN;

System options for debugging macros These five system options affect the kinds ofmessages SAS writes in your log. The default settings appear in bold.MERROR | NOMERROR when this option is on, SAS will issue a warning ifyou invoke a macro that SAS cannot find.SERROR | NOSERROR when this option is on, SAS will issue a warning ifyou use a macro variable that SAS cannot find.MLOGIC | NOMLOGIC when this option is on, SAS prints in your logdetails about the execution of macros.MPRINT | NOMPRINT when this option is on, SAS prints in your log thestandard SAS code generated by macros.SYMBOLGEN | NOSYMBOLGEN when this option is on, SAS prints in your log thevalues of macro variables.

OPTIONS MPRINT NOSYMBOLGEN NOMLOGIC;

SAS\SQL

proc sql;create table a asselect * from sashelp.class;quit;PROC PRINT;RUN;

proc sql;create table a1 asselect * from sashelp.classwhere sex ='F' and age =13; title 'Personnel Data';quit;PROC PRINT;RUN;

proc sql;create table work.new asselect name,age,sex from sashelp.class;quit; PROC PRINT;RUN;

proc sql;create table work.new1 asselect name,age,sex from sashelp.class

where sex = 'F' and age = 13; title 'Personnel Data';quit; PROC PRINT;RUN;

*-------label-----;

proc sql;create table work.b asselect name label ='fname',sex label='gender' from sashelp.class;title 'Personnel Data';quit;PROC PRINT;RUN;

proc sql;create table work.c as select name,age from sashelp.classwhere age >= 14;quit;PROC PRINT;RUN;

proc sql;DELETE from awhere age = 11;quit;

data s;input status$ num;cards;a 1b 2 c 3d 4;

data v;input status$ ran;cards;a 2 c 3d 5;

data f;set s;set v;by status;run;proc print;run;

proc sql;create table h as select* from s,v where s.status=v.statusorder by status;

quit;proc print;run;

proc sql;create table ss asselect * from s natural join v;quit;PROC PRINT;RUN;

data Z;merge s(in=s) v(in=v);by status;IF s AND v;run;PROC PRINT;RUN;

data one;input name$ pos;cards;a 1b 2 c 3d 4;

data two;input name$ rank;cards;a 2 c 3d 4;

data both;set one two;run;

PROC PRINT;RUN;

proc sql;create table bth1 asselect*from oneouter union select*from two;quit;PROC PRINT;RUN;

proc sql;create table bth asselect*from oneouter union corrselect*from two;quit;PROC PRINT;RUN;

data c;input name$ pos;cards;a 1b 2 c 3d 4;

data d;input name$ rank;cards;a 1 c 3e 5;

proc sql;create table k asselect * from c inner join don c.name=d.nameorder by name;quit;PROC PRINT;RUN;

proc sql;create table bth3 asselect * from one natural join two;quit;PROC PRINT;RUN;

data both;set s v;/*by status;*/run;PROC PRINT;RUN;

data z1;merge one two;by name;run;PROC PRINT;RUN;

data Z2;merge one(in=a) two(in=b);by name;IF a or b;run;PROC PRINT;RUN;

proc sql;create table z3 asselect a.*,b.*from one as a full join two as bON a.name=b.name;

quit;PROC PRINT;RUN;

proc sql;create table OYE asselect name,age from work.cexceptselect name,age from work.d;quit;PROC PRINT;RUN;

proc sql;create table dd (drop=height) as select * from sashelp.class;quit;PROC PRINT;RUN;

proc sql;create table dsp1 as select * from sashelp.class where SEX='F' ORDER BY AGE;quit;PROC PRINT;RUN;

*----insert------;

proc sql;create table z as select * from sashelp.class;insert into z values ('sach','M',28,72,57);quit;PROC PRINT;RUN;

data Employees; input @1 IdNum $4. @7 LName $11. @18 FName $11. @29 JobCode $3. @33 Salary 5. @39 Phone $12.; datalines;1876 CHIN JACK TA1 42400 212/588-56341114 GREENWALD JANICE ME3 38000 212/588-10921556 PENNINGTON MICHAEL ME1 29860 718/383-56811354 PARKER MARY FA3 65800 914/455-23371130 WOOD DEBORAH PT2 36514 212/587-0013;

proc sql; alter table employees modify salary format=dollar8. add position num format=8.; drop phone;quit;PROC PRINT;RUN;

data Employe;

input @1 IdNum $4. @7 LName $11. @18 FName $11. @29 JobCode $3. @33 Salary 5. @39 Phone $12.; datalines;1876 CHIN JACK TA1 42400 212/588-56341114 GREENWALD JANICE ME3 38000 212/588-10921556 PENNINGTON MICHAEL ME1 29860 718/383-56811354 PARKER MARY FA3 65800 914/455-23371130 WOOD DEBORAH PT2 36514 212/587-0013;

proc sql; select * from Employe; update employe set salary=salary+

case when JobCode='TA1' THEN 1000 ELSE 2000 END;

quit;PROC PRINT;RUN;

data sale;input OrderID @3 OrderDate mmddyy10. OrderPrice OrderQuantity CustomerName$;format orderdate mmddyy10.;cards;1 12/22/2005 160 2 Smith2 08/10/2005 190 2 Johns3 07/13/2005 500 5 Baldw4 07/15/2005 420 2 Smith5 12/22/2005 100 4 Wood6 10/02/2005 820 4 Smith7 11/03/2005 200 2 Baldw;

PROC PRINT;RUN;

proc sql;SELECT CustomerName, SUM(OrderPrice) AS GPRICE FROM SaleGROUP BY CustomerName ORDER BY 1;

quit;

proc sql;SELECT CustomerName, SUM(OrderPrice) AS GPRICE FROM SaleGROUP BY CustomerName ORDER BY 2;

quit;

proc sql;SELECT CustomerName, SUM(OrderPrice) AS GPRICE FROM SaleGROUP BY CustomerName HAVING gprice>1000;

quit;proc sql;SELECT CustomerName, MEAN(OrderPrice) AS ORDERMEAN FROM SaleGROUP BY CustomerName ;

quit;

proc sql;SELECT CustomerName,OrderPrice FROM SaleORDER BY CustomerName ;

quit;

SAS

BASE PROGRAMMING

Created by SACHIN VERMA (SAS Base certified)