Home 
Proc SQL dataset maintenance
Proc SQL dataset maintenance
Proc SQL dataset maintenance
![[Download]](/images/stories/Icons/download.gif "Download"){kind=icon}
![[Print]](/images/stories/Icons/print.gif "Print"){kind=icon}
%******************************************************************************; %** http://www.info-net.nl **; %******************************************************************************; %* Topic : SAS Procedures *; %* Program : Proc SQL Dataset maintenance *; %* Author : Raymond Ebben *; %* Location : http://www.info-net.nl/index/sas-coding/sas-procedures *; %* Date : January 2006 *; %* Version : 1.0 *; %* Description : This program demonstrates dataset maintanence with proc SQL*; %* - Create empty datasets *; %* - Table modification *; %* - Integrety constraints *; %* - Inserting data *; %* - Removing data *; %* - Modifying data *; %* - Creating index *; %* - Describing tables *; %******************************************************************************; %* In datasets : None *; %* In macrovars : None *; %* In files : None *; %* Out datasets : work.example1 work.example2 *; %* Out macrovars : None *; %* Out files : None *; %******************************************************************************; %** Version control **; %******************************************************************************; %* Mod * Ver. * Date * Description *; %******************************************************************************; %******************************************************************************; %******************************************************************************; %* Create empty datasets *; %* This example will create 3 datasets: example1 and 2 are empty, and example3*; %* is created as a copy of sashelp.class *; %* These datasets will be used troughout this program *; %******************************************************************************; proc sql noprint; create table work.example1 ( /*varname type size format label */ idvar num label='Key variable', name char (10) label='Char. variable with length 20', date num format=date9. label='Num. variable with date9 format', date2 num format=date9. label='Num. variable with date9 format' ); create table work.example2 ( /*varname type size format label */ idvar num label='Key variable', name2 char (100) label='Char. variable with length 20', date2 num format=date9. label='Num. variable with date9 format' ); create table work.example3 as select * from sashelp.class; quit; %******************************************************************************; %* Table modification *; %* This will drop the name2 variable on example1, reset the length of example2*; %* from 100 to 10 and drop the entire table example3 *; %******************************************************************************; proc sql noprint; alter table example1 drop date2; alter table example2 modify name2 char(10); drop table example3; quit; %******************************************************************************; %* Integrety constraints *; %* This will Create an integrety constraint for example1 to filter the data *; %* before inserting into the table. This constraint will reject non-missing *; %******************************************************************************; proc sql noprint; alter table example1 add constraint filled not null(name) message='Value raised for update/insert cannot be missing; Record is omitted' MSGTYPE=USER; quit; %******************************************************************************; %* Inserting data *; %* This will insert observations into work.example1 using the values statement*; %* The first statement will fail because of name being missing in the fifth *; %* values statement; therefore the insert is performed twice *; %* (once without the incorrect record) to fill the dataset for other examples.*; %* Furthermore data is inserted in work.example2 using the SET statement *; %******************************************************************************; proc sql noprint /*undo_policy = none*/; /*This option would prevent the records entered successfully to be kept, but for the example the default is kept: all entries are rejected */ insert into example1 values(1,'One' ,'01JAN2003'd) values(2,'Two' ,'01FEB2003'd) values(3,'Three','01MAR2003'd) values(4,'Fuor' ,'01APR2003'd) /*Typo to be fixed later on*/ values(5,'' ,. ); insert into example1 values(1,'One' ,'01JAN2003'd) values(2,'Two' ,'01FEB2003'd) values(3,'Three','01MAR2003'd) values(4,'Fuor' ,'01APR2003'd);/*Typo to be fixed later on*/ insert into example2 set idvar=1, name2='One' , date2='01JAN2003'd set idvar=2, name2='Two' , date2='01FEB2003'd set idvar=3, name2='Three', date2='01MAR2003'd set idvar=4, name2='Fuor' , date2='01APR2003'd /*Typo to be fixed later on*/ set idvar=5, name2='' , date2=.; quit; %******************************************************************************; %* Removing data *; %* This example will remove the all observations in work.example1 where name *; %* is missing, and all observations from work.example2 where name2 is missing.*; %* Note that for example1 this record was never added thus no missing present *; %******************************************************************************; proc sql noprint; delete from example1 where name=''; delete from example2 where name2=''; quit; %******************************************************************************; %* Modifying data *; %* This example will fix the type created in the insert example. *; %* Thus for both work.example1 and work.example2 'Fuor' will be set to 'Four' *; %******************************************************************************; proc sql noprint; update example1 set name='Four' where name='Fuor'; update example2 set name2='Four' where name2='Fuor'; quit; %******************************************************************************; %* Creating index *; %* This example will create several index. *; %* An index called 'date' will be created on the date value in work.example1 *; %* An index called 'date2' will be created on the date2 value in work.example2*; %* An index called 'namedate' will be created on the name and date value in *; %* work.example1; *; %* An index called 'namedate' will be created on the name2 and date2 value in *; %* work.example2; *; %* An index called 'idvar' will be created on the idvar value in work.example1*; %* The recently created index called 'idvar' will be removed *; %* Note that when more than one value is used, the index name does not need *; %* to match the variable name anymore *; %******************************************************************************; proc sql noprint; create index date on example1 (date); create index date2 on example2 (date2); create index namedate on example1 (name, date); create index namedate on example2 (name2, date2); create index idvar on example1 (idvar); drop index idvar from example1; quit; %******************************************************************************; %* Describing tables *; %* This example will present a table description to the log for both *; %* work.example1 and work.example2 *; %******************************************************************************; proc sql noprint; describe table example1; describe table example2; quit;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204