• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Oracle Data set procedure in SAS

Data set procedure

This is also SAS base concept. This procedure belongs to sas base module. Using data set procedure we can

  1. Rename the data set and sas files
  2. Exchange the data between the data sets
  3. Copy the sas files from one library to another library
  4. Modify the data sets
  5. Change the formats
  6. Assign constraints
  7. Delete constraint
  8. Append the data set value from one data set to another
  9. We can report descriptive information for required library

Rename the data sets and sas files 

Change statement: can be used to rename the data set

Syntax:

Change old name = new name :

Eg:

Proc data set library = work ;
Change demo = demographic;
Quit;

Exchange statement

Can be used to exchange between the data sets

Proc data set library = work ;
Change one = demo1;
Quit;

If you want to exchange between the sas files the 2 sas file must be available in the same library

Copy the sas files 

Copy statement:

Can be used to copy the sas files between the libraries or we can transfer sas files between libraries                        

Eg:

Proc data set library = sas user;
Copy in = sas user out = work;
Quit;

Memtype option:

Can be used to copy the required sas files type or required mam type. Values of mem type = all, data, view, cat

  • We can copy the required sas files using with select and exclude statement
  • Exclude statement includes non required sas files

Eg:

Proc dataset library = sas user;
Copy in = sas user
Out = work mam type = data;
Select demo 5 one;
Quit;

Modify the data sets 

Modify statement:

Can be used to change the format

a)   Change the formats:

Eg:

Proc data sets library sashelp;
Modify buy;
Format amount dollar 12;
Quit;

b)   Assign the constraints:

Data one;
Infile ‘d:\data1.txt’;
Input stno age gender $ color $;
Run;
Proc sql;
Describe table one;
Quit;
Data demo;
Length stno 4 age 3
Gender $ 7 color $5;
Delete;
Run;

 /* Assign constraints */

proc datasets library = work;
modify demo;
ic create nt UK = unique(stno);
ic create nt NK = not null(gender);
ic create nt CK =check (where = (color = ‘white’));
quit;
proc data sets library = work;
append base = demo data = one
force;
quit;

Appending statement

Using appending statement we can append the values or load the values from one data set to other. Append statement can be used only in dataset procedure

These core tutorials will help you to learn Oracle Data set procedure in SAS . For an in-depth understanding 
and gain practical experience,  explore SAS Training.

 /* To link up the tables using with dataset procedure */

1st import the data
Data demo1;
Length stno 4 age 3
Gender $6 color $6;
Delete;
Run;
Data lab 1;
Length stno 4 test $6
Units 5;
Delete;
Run;
Proc datasets library =work ;
Modify demo 1;
Ic create pk = primary key (stno);
Modify lab 1;
Ic create fk = foreign key (stno)
References demo1;
Quit;
Proc sql;
Describe table demo1;
Describe table lab1;
Quit;
Proc datasets library = work;
Append base = demo 1 data = one force;
Append base = lab 1 data = two force;
Quit;

Delete the constraints       

Ic delete statement;

Can be used to delete the constraints if we use – all – in ic delete statement, it delete all constraints from the table if we want to delete required constraint name.

 Eg:

Proc data sets
Library =server;
Modify demo1;
Ic delete pk;
Quit;

To report descriptive information for rwquired library:

Details options:

Can be used to report descriptive information for required library

Eg:

Proc datasets library = sasuser
Details;
Quit;

Contents statement:

Can be used to report descriptive information for required datasets

Eg:

Proc datasets library = server;
Contents data = demol;
Quit;

Delete statement:

Can be used in dataset procedure and it can delete required dataset from library in data set procedure

Eg:

Proc datasets library = sas user;
Delete demo 5;
Quit;

Syntax:

Delete <non-req sas file names>;

Save  statement:

Can be used to keep required sas file in library

 Syntax:

       Save <rq sas files name >;

 After lib name concept Analysis procedure:

Sort procedure:

Can be used to sorting analysis means we can arrange the data in to certain order (ascending or descending) two types of sorting

  1. Number sorting
  2. character sorting

Number sorting:

By statement:

Requires sorting variable we can’t run sort procedure with out by statement default it produce ascending order if we want descending order we will use descending option in by statement       

Out option:

Can be used to create new datasets in procedure block and stores required data

Note:

If we run the sort procedure without option the master dataset will be changed into sorting order.

Sort procedure does not have reporting capability

Eg:

Data demo;
Input pig page;
Cards;
100         34
104         23
102         34
103         26

/* Ascending order */

Proc sort data = demo out = demo;
By pid;
Run;
Proc print data = demo1;
Run;

/* Descending order */

Proc sort data = demo out = demo 2;
By descending pid;
Run;
Proc print data = dem2;
Run;

NOTE

If data set has a unique observation we can’t use multiple sorting variables if it has grouping data (categorical data) we can use multiple sorting variables

Character sorting

Capture

Eg:

Data one;
Input x $;
Cards;
A
a
*
;
Proc sort data = one out = two;
By x;
Run;
Proc print data = two;
Run;

Eg:

Data trtment;
Input center $ trail $ sub;
Cards;
Appolo                    phase 1                 56
Nims                      phase 1                 67
Care                      phase 1                 34
Appolo                    phase 2                 234
Nims                      phase 2                 245
Care                      phase 2                 256

/* For ascending 8/

Proc sort data = treatment
Out = treatment 1;
By center;
Run;
Proc print data = treatment 1;
Run;

/* For descending */

Proc sort data = treatment
Out = treatment 2;
By descending center;
Run;

Unnecessary data:

Duplicate data value:

The same data value repeated in that variable so it is called duplicate data value duplicate data value can find out based on required variable (Acc.no, pid, pfno, empid tec)

Duplicate observations:

The same observation repeated MIISING PG :147. Based on all data values

Nodup key:

Using no dup key we can eliminate duplicate data values & duplicate observations from required data set

Eg:

Data demo;
Input subid age height vdate : date 9;
Format vdate date 9;
Cards;
100         45           5.6          13 aug 2006
101         56           4.5          13 aug 2006
102         45           4.9          14 aug 2006
101         23           5.6          14 aug 2006
;
Proc sort data = demo out = demo 1 no dup key;
Bu subid;
Run;
Proc print data = demo 1;
Run;

Eg:

Data med;
Input subid drug $
Sdate = date 9;
Format sdate date 9;
Cards;
100         col5mg                  13 aug 2006
101         col5mg                  13 aug 2006
102         col10mg                 13 aug 2006
101         col10mg                 13 aug 2006
102         col15mg                 13 aug 2006
101         col10mg                 13 aug 2006
;
Proc sort data = medi out = medi 1
No dup key;
By subid drug sdate;
Run;
Proc print data = medi1;
Run;

Note:

Using no dup ; nodupress and no dup key we can eliminate duplicate observations

PRINT PROCEDURE:

It is a simplest reporting tool print procedure default destination is output window this report (or) output is called listing output

Output window is also called list files

Options:

1)Noobs:

Using noobs option we can remove obs column from the output default is obs

Eg:

Data trt;
Input Gid $ drug $ week sub;
Cards;
G100      col5mg                  3              120
G200      col5mg                  3              120
G300      col5mg                  3              120
G100      col10mg               6              120
G200      col10mg               6              120
G300      col10mg               6              120
G100      col15mg               9              120
G200      col15mg               9              120
G300      col15mg               9              120
;
Proc print data = tet noobs;
Run;

2)Double:

Using this option, we can give gap between the observation

Eg:

Proc print data = trt double;
Run;
  • Based on requirement, we can use multiple options

Eg:

Proc print data = trt  noobs,  double;
Run;

3)Heading:

Using this option, we can change the column heading format (horizontal/vertical) for reporting

Eg:

Proc print data = trt;
Heading = vertical;
Run;

4)Width:

Using this option, we can give gap between the column (minimum/full)

Eg:

Proc print data = trt
Width = full;
Run;

Statement:

1)Var:

Using this statement we can report required variables in specific order

Eg:

Print data = trt;
Var drug gid sub;
Run;

2)Sum:

Using this statement, we can do column wise sums for reporting

Eg:

Proc print data = trt;
Sum sub;
Run;

3)Id:

Using this statement, we can replace the obs column

Eg:

Proc print data = trt;
Id drug;
Run;

4)Null id:

Using this statement, we will get same result of the noobs option

Eg:

Proc print data = trt;
Id;
Run;

5) Label Staement: Using this staement, we change the variable names for reporting.

Eg:

Proc print data = trt label;
label gid = groupid
drug = 'Control split character  drug';
run;

–> Using split character, we can give breaks in the column headings (labels or variable names). Default split character is blank.

Split Option: It can be used to indicate required split character.

Eg:

Proc print data = trt label split;
label gid = groupid
drug = 'control drug * name';
run;

–> Labels only for reporting, if e write label statement in procedure block , these labels are temparary labels. Data set block – permanent labels.

Eg:

Proc datasets library = sasuser;
delete demo 5;
quit;

Syntax: delete <non-req SAS file names>;

Save Statement: It can be used to keep required SAS files in library.

Syntax: Save <rq SAS files name>;

Transport Procedure: It is a SAS BASE procedure. We can transport the dataset for reporting , use with transpose procedure. Using transpose procedure, we can convert datavalues as variables and variables as a data values.

id Statement: It requires which data variable values to transpose or convert as a variables.

Var Statement: It requires which variable values to convert or transpose as observation or data values.

Prefix Option: It can be used to add required test for variable.

Procedure and it indicates var statement variable as a data values. If we want to rename ths variable we will use name option.

Eg:

datalab;
input stno test $ units;
cards;
100 hr 90
101hr 89
100 dbp 98
100 dbp 97
100 sbp 156
100 sbp 167
;
proc sort data = lab;
by stno;
run;
proc transpose data = lab
out = lab1 name = details
prefix = Test_;
id test;
var units;
by stno;
run;
proc print data = lab1;
run;

Reporting                                                            Rawdata

Group col_0.05  col_0.1  col_0.15              Group         drug      sub

G100       75              70             60                    G100         0.05       75

G100       80              75             70                    G100         0.05       75

Drug           G100           G200                          G100         0.1          70

col5mg          75                80                             G200         0.1         75

col10mg        70                75                             G100          0.15       60

col15mg        60                70                            G200          0.15       70

SAS/CONNECT:

Using SAS/CONNECT modual concept loc can transfer the SAS files between the environments. i.e., windows to unix/windows to windows/unix to windows/windows to mainframes and old version (V6) to new version (V9).

CPORT PROCEDURE: It can be used to convert sas file binary format.

CIMPORT PROCEDURE: It can be used to convert binary file in SAS format.

Capture

Datasets, catalogue and libraries only we can convert into binary format.

Eg:

Proc Cimport data = sashelp.class
infile = 'd:\ class.txt' data = demo;
run;

/* To convert req.library  in binary format */

Eg:

Proc Cport
lib = sas User
file = 'd:\ User.cpt';
run;

Use with memtype option , we can indicate require SAS file options.

Eg: 

Proc Cport
lib = sas User
file = 'd:\ User.cpt';
memtype = data;
select one feeder feedolet;
run;

Using Select Statment: We can indicate require SAS files.

/* To convert binary format into SAS file */

Eg:

Proc Cimport
infile = 'd:\ User.cpt'
lib = work;
run;

If we want to convert required SAS file in binary format (or) transport format we will write memtype option and select statement in cport procedure.

We want to import required SAS files from binary file we will write memtype option &       statement in cimport procedure.

Eg:

Proc Cimport
infile = 'd:\ User1.cpt'
lib = work memtype = cat;
Select formats;
run;

SAS/BASE CONCEPT:

To create V6 version data use with V7, V8.0, V8.1,  V8.2,  V9.0, V9.1, V9.1.2, V9.1.3

                       V6                                                                            New Technology

– variable names upper case only                                          – It allows variable names in any case

– dataset extension name is .sd2                                                        – Data set extension name is .sas7bdat

– catalog extenison name is sc2                                             – catalog extension name is .sas7bcat

– Views extension name is .sas7bview.

Engine: Using engine option we can store the data in SAS dataset in required format.

Libname < libref > < engine > ‘path’;

Eg:

libname trail V6 'd:\ apple';
data trail.demo;
input pid age;
cards;
100   67
101   34
;
proc contents
data = trail.demo;
run;

For indepth knowledge on SAS, click on below

Summary
Review Date
Reviewed Item
Oracle Data set procedure in SAS
Author Rating
4

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Oracle Data set procedure in SAS"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.