Hi,
Pass and fail information is extracting every day from source system table called X to table A
Now Table A has 10+ years (increasing day by day) history (result variable has pass and fail info) so
1) I want to delete all the fail information and keep pass information all the time from Table A and want to keep only 6 months information of fails in table A every time.(pass info all the time and fails only for 6 months in table A)
data a B; set A; if Result="F" then output b ; else if Result="F" and dt>=intnx('month',today(),-6,'s')then output a ;
I think I am missing Pass information here.
2) And save all deleted fail information in table B and I want to keep only 4 years of fail information all the time in table B.
logic is not correct ,I think
data B; set b; if Result="F" and dt>=intnx('year',today(),-4,'s'); run;
This process should be automated every day.
Thanks,
SS
I think I am missing Pass information here.
Yes. And if this code is actually what you ran:
data a B; set A; if Result="F" then output b ; else if Result="F" and dt>=intnx('month',today(),-6,'s')then output a ;
You will have to start over, hopefully with a backup of data set A. You possibly just created an empty data set A since there are no "else if Result="F" and " records. All the F went to b. I would check your log or other method to see if A has zero observations.
Use of the code structure is very risky as it will replace the data set A. If you have an incorrect record selection then you will have records gone and you hopefully can rebuild A from something.
data A; set A;
I suspect it might help to use some actually descriptive data set names as you are throwing A and B around a lot and the words of your description are a little confusing because of repeated use of the same reference but should in actuality be different data sets.
May be something like:
Data failall fail4year fail6month pass; set longtermdata; if result='F' then output failall; if result='F' and dt ge intnx('month',today(),-6,'S') then output fail6month; if result='F' and dt ge intnx('year',today(),-4,'S') then output fail4year; if result ne 'F' then output pass; run;
What I would be doing to avoid constant table growth and performance degradation while still having all the data available:
- create monthly slices (separate tables per month)
- create views over the monthly slices to support the most common queries
To get there you would now have to split your big table into monthly tables and create the views one off process.
You would have to change your load process to create and load further monthly slices and update the views as an ongoing process.
To give you an idea how this could look like, below some code which splits a big table into monthly tables and then creates a view over some of the monthly slices. The code is dynamic and would work for any dates in the source table.
/** create sample HAVE data **/
data have;
format dt date9.;
do dt=intnx('year',today(),-10) to today();
status='fail'; output;
status='pass'; output;
end;
run;
/** split sample data into monthly tables **/
proc sql;
create table _dist_months as
select distinct(put(dt,yymmn.6 -l)) as yymm length=6
from have
;
quit;
/* generate code to split the data */
filename codegen temp;
data _null_;
/* file print;*/
file codegen;
put @1 'data';
do i=1 to nobs;
set _dist_months nobs=nobs;
put @3 'Fail_' yymm 'Pass_' yymm;
end;
put @3 ';';
put @3 'set have;';
put @1 'if status="fail" then' /
@3 'do;' ;
do i=1 to nobs;
set _dist_months nobs=nobs;
if i=1 then
do;
put @5 'if put(dt,yymmn6.)="' yymm +(-1) '" then output Fail_' yymm ';';
end;
else
do;
put @5 'else if put(dt,yymmn6.)="' yymm +(-1) '" then output Fail_' yymm ';';
end;
end;
put @3 'end;';
put @1 'else if status="pass" then' /
@3 'do;' ;
do i=1 to nobs;
set _dist_months nobs=nobs;
if i=1 then
do;
put @5 'if put(dt,yymmn6.)="' yymm +(-1) '" then output Pass_' yymm ';';
end;
else
do;
put @5 'else if put(dt,yymmn6.)="' yymm +(-1) '" then output Pass_' yymm ';';
end;
end;
put @3 'end;';
put @1 'run;';
stop;
run;
/* execute generated code */
%include codegen /source2;
/** Create views over monthly slices **/
/* as an example: view V_Fail6 for last 6 months of fails */
proc sql;
create table dslist as
select libname, memname
from dictionary.tables
where libname='WORK' and scan(memname,1,'_') = 'FAIL'
order by memname DESC;
;
quit;
/* generate the code */
filename codegen temp;
data _null_;
file codegen;
set dslist end=last;
if _n_=1 then
do;
put
'proc sql;' /
' create view V_Fail6 as' /
' select * from ' libname +(-1)'.' memname
;
end;
else
put ' union corr all select * from ' libname +(-1)'.' memname;
if last or _n_=6 then
do;
put
' ;' /
'quit;'
;
stop;
end;
run;
/* execute the code */
%include codegen / source2;
Creating monthly slices has also the advantage that you never will have to delete records in these tables. You just insert/append new records.
For archiving purposes: You just zip, move, delete or whatever monthly slices (=files on disk).
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: