*Modifying a data set;

data nonprof;
input @1 memberid @5 Name $30. @36 since @41 years @44 Donations;
format donations dollar5.0;
label memberid="ID #" since="Member Since" years="Total Years Membership";
datalines;
102 James and Roberta Edwards      2008 2  20
 23 Michaels Family                2003 7  30
  8 Charles Dobbs                  2003 7  20
 46 Eliza Singleton                2004 6  75
113 Mullen Jeffords                2010 0  20
;
run;

proc print label;
run;

data nonprof;
modify nonprof;
years=year(today())-since;
run;

proc print label;
run;

*Modifying a data set with a transactional data set;

data ytd;
input @1 memberid @5 Name $30.  since 36-39 years 41-42 Donations 44-46;
*Make sure the first line is padded with spaces;
datalines;
102 Drs. James and Roberta Edwards            
 23                                         20
  8                                         15
129 Bill Mauldin                   2011     40
;
run;
proc print;
run;

proc sort data=ytd;
by memberid;
proc sort data=nonprof;
by memberid;

data nonprof;
update nonprof ytd;
by memberid;
run;

proc print data=nonprof label;
run;

*Transaction data set with duplicate values;

data ytddup;
input @1 memberid @5 Name $30.  since 36-39 years 41-42 Donations 44-46;
*Make sure the first line is padded with spaces;
datalines;
102 Drs. James and Roberta Edwards            
 23                                         20
  8                                         15
129 Bill Mauldin                   2011     40
  8                                        100
;
run;
proc sort data=ytddup; by memberid;

data nonprofdup;
update nonprof ytddup;
by memberid;
run;

proc print data=nonprofdup label;
run;

*Transaction data set with duplicate values--fix with index;
*Assign a different name to 2012 donations;
*Modify the data set to avoid problems with index;

data ytddup;
input @1 memberid @5 Name $30.  since 36-39 years 41-42 Donate12 44-46;
*Make sure the first line is padded with spaces;
datalines;
102 Drs. James and Roberta Edwards           0
 23                                         20
  8                                         15
  8                                        100
;
run;

*Create the index;
proc datasets;
modify nonprof;
index create memberid/unique;
run;

proc sql; select * from nonprof; quit;

*Update with a key;
data nonprof;
set ytddup;
modify nonprof key=memberid/unique;
donations+donate12;
run;

proc print data=nonprof;
run;

*We can also rename variables from the transaction data set on the run;

data ytddup;
input @1 memberid @5 Name $30.  since 36-39 years 41-42 donations 44-46;
*Make sure the first line is padded with spaces;
datalines;
102 Drs. James and Roberta Edwards           0
 23                                         20
  8                                         15
  8                                        100
;
run;

*Create the index;

proc datasets;
modify nonprof;
index create memberid/unique;
run;

data nonprof;
set ytddup (rename=(donations=donate12));
modify nonprof key=memberid/unique;
donations+donate12;
run;

proc print data=nonprof;
run;

*Example of output/replace/remove;

data nonprof;
input @1 memberid @5 Name $30. @36 since @41 years @44 Donations;
format donations dollar5.0;
label memberid="ID #" since="Member Since" years="Total Years Membership";
datalines;
102 James and Roberta Edwards      2008 2  20
 23 Michaels Family                2003 7  30
  8 Charles Dobbs                  2003 7  20
 46 Eliza Singleton                2004 6  75
113 Mullen Jeffords                2010 0  20
;
run;

data ytddup;
input @1 memberid @5 Name $30.  since 36-39 years 41-42 donations 44-46;
*Make sure the first line is padded with spaces;
datalines;
102 Drs. James and Roberta Edwards           0
 23                                         20
  8                                         15
  8                                        100
129 Bill Mauldin                   2011     40
;
run;

proc datasets;
modify nonprof;
index create memberid/unique;
run;

data nonprof;
set ytddup (rename=(donations=donate12 name=name12));
modify nonprof key=memberid/unique; 
if _IORC_=%sysrc(_sok) then do;
if donate12=0 then do;
donations=-99;
name=name12;
end;
*I'd like to output this value, but it was very delicate;
else
donations+donate12;
replace;
end;
else if _IORC_=%sysrc(_dsenom) then do;	*We can use this with the KEY option;
donations=donate12; *Doesn't handle cumulative donations;
name=name12;
output;
_error_=0; *Prevents error message from being written to LOG;
end;

run;

proc print data=nonprof label;
run;

*Read in data set and set up an integrity constraint;
data lab2012;
input client $16. @18 email $21. @40 degree $ @49 USC $1. @51 Dept 
$15. / date date9. +1 Gratis $1. +1 Consultant $10.;
datalines;
Jane Lipschitz   lipschitzj@dnr.sc.gov External N SCDNR 
25Jan2012 Y John Grego 
Gerry Bainbridge bainbrid@email.sc.edu Faculty  Y School of Music
31Jan2012 Y John Grego 
Scott Tyler      tylers@jonesctr.org   External N JERC
17Jan2012 N John Grego
Erin Merrick                           Masters  Y Environment
07Jan2012 Y Wilma Sims
;
run;

proc datasets nolist;
modify lab2012;
ic create check_USC=check(where=(USC in ('N' 'Y' 'y' 'n')))
message="Incorrect code for USC";
quit;

proc sql;
insert into work.lab2012
values ('Erin Merrick','merrickj@email.sc.edu','Masters','Z','Environment','07Jan2011'd,'Y','Wilma Sims');
quit;

*Audit trail--make sure nonprof is in WORK;

proc datasets nolist;
audit nonprof;
initiate;
quit;

data nonprof;
modify nonprof;
years=year(today())-since;
run;

proc datasets nolist;
audit nonprof;
suspend;
quit;

proc contents data=nonprof (type=audit);
run;

*Look at audit file;
proc print data=nonprof (type=audit);
run;

*Stop the audit and delete the audit file;

proc datasets nolist;
audit nonprof;
terminate;
run;

*Create generations data sets for nonprof, which should be newly loaded into work;

proc datasets nolist;
modify nonprof (genmax=4);
quit;

* #001 will have original data set, while nonprof will be updated;
data nonprof; set nonprof; 
years=year(today())-since;
run;

*Nonprof is sorted--neither #001 or #002 is sorted--#001 is original data set, while #002 has years updated;
proc sort data=nonprof;
by memberid; run;

*Prints updated years;
proc print data=nonprof (gennum=2); run;
*Prints original data set;
proc print data=nonprof (gennum=1); run;


*Prints sorted data set with updated years;
proc print data=nonprof (gennum=0); run;

*Prints two generations ago;
proc sgplot data=nonprof(gennum=-2);
histogram years; 
run;

*Delete all generations;
proc datasets nolist;
delete nonprof (gennum=all);
run;