remove variable

[anu2019]

RUNTIME	RunName	RunTimeStamp	RUN_INST_NUM_SIMULATION
13:26	CECL_CUBE_AUTO02_20190201_S99	24Jun13:26	0
13:27	CECL_ECL_AUTO02_20190201_S99	24Jun13:27	100

I want to remove RunName variable which have "CUBE" in the variable

Kindly help

[arthurcavila]

data mydata;
input myvar $20.;
lines;
textcubetext
texttexttext
;
run;

data mydata2;
set mydata;
where myvar not like '%cube%';
run;

[Astounding]

It's not clear what you mean by "remove the variable".  Do you want to give the variable a missing value?  Do you want to remove the entire observation from the data set?  Depending on what the answer is, you could use one of the IF THEN statements below:

data want;
   set have;
   if index(RunName, 'CUBE') then delete;
   if index(RunName, 'CUBE') then RunName = ' ';
run;

The code here looks specifically for uppercase letters ("CUBE").  If you want lower-case letters to be located as well, the program can easily be tweaked to allow that.

[anu2019]

Hii

Want to remove observation if run name have CUBE

[arthurcavila]

Look at my previous sample code. You may need to adjust the where condition to match case.

[andreas_lds]

---

@anu2019 wrote:
Hii

Want to remove observation if run name have CUBE

---

At least three possible solutions exist:

• where-statement (see @arthurcavila  post)
• if + delete as posted by @Astounding 
• subsetting-if (example below)

You have to take care of possible differences in spelling, "CUBE" is not the same as "cube".

data want;
  set have;
  
  if not index(upcase(RunName), 'CUBE');
run;

Could the word "cube" be part of another word, so that the observation should not be deleted?

[anu2019]

its working now

Thank you so much

[anu2019]

HI Below is my code

libname sm "/sas/test/saswork";
%let workgroup=/sas/test/sasconfig/Lev2/AppData/SASRiskWorkGroup/groups/Retail;
%let WHLworkgroup=/sas/test/sasconfig/Lev2/AppData/SASRiskWorkGroup/groups/Wholesale;

%let RunMonth=Jun;
%let RunDate=26;
/*data _null_;*/
/*call symput('RunMonth',substr(strip(put(today(),monname.)),1,3));*/
/*call symput('RunDate',strip(put(today(),day.)));*/
/*run;*/
/*%put &RunMonth;*/
/*%put &RunDate;*/

%let runInstances=&workgroup./SASModelImplementationPlatform/output/run_instances ;
%let WHLrunInstances=&WHLworkgroup./SASModelImplementationPlatform/output/run_instances ;

filename runInst pipe "ls -al &runInstances | sed -r s'/\s+/|/g'" ;
filename WHLInst pipe "ls -al &WHLrunInstances | sed -r s'/\s+/|/g'" ;
%put &runInstances;
%put &WHLrunInstances;
/* X "ls -al &runInstances > /sas/dev/saswork/afolderlist.txt" ;
filename runInst pipe "/sas/dev/saswork/afolderlist.txt" ; */

DATA WORK.folderlist;
LENGTH
F1 $ 10
F2 8
UserName $ 16
Domain $ 6
F5 $ 12
F6 8
RunMonth $ 12
RunDate $ 2
RunTime $ 40
RunName $ 40 ;
FORMAT
F1 $CHAR10.
F2 BEST5.
UserName $CHAR16.
Domain $CHAR6.
F5 $CHAR12.
F6 BEST7.
RunMonth $CHAR12.
RunDate $CHAR1.
RunTime $CHAR40.
RunName $CHAR40. ;
INFORMAT
F1 $CHAR10.
F2 BEST5.
UserName $CHAR16.
Domain $CHAR6.
F5 $CHAR12.
F6 BEST7.
RunMonth $CHAR12.
RunDate $CHAR2.
RunTime $CHAR40.
RunName $CHAR40. ;
INFILE runInst
LRECL=121
DLM='|'
truncover
DSD ;
input @ ;

INPUT
F1 : $CHAR10.
F2 : ?? BEST5.
UserName : $CHAR16.
Domain : $CHAR6.
F5 : $CHAR12.
F6 : ?? BEST7.
RunMonth : $CHAR12.
RunDate : $CHAR2.
RunTime : $CHAR40.
RunName : $CHAR40. ;
RUN;

DATA WORK.WHLfolderlist;
LENGTH
F1 $ 10
F2 8
UserName $ 16
Domain $ 6
F5 $ 12
F6 8
RunMonth $ 12
RunDate $ 2
RunTime $ 40
RunName $ 40 ;
FORMAT
F1 $CHAR10.
F2 BEST5.
UserName $CHAR16.
Domain $CHAR6.
F5 $CHAR12.
F6 BEST7.
RunMonth $CHAR12.
RunDate $CHAR2.
RunTime $CHAR40.
RunName $CHAR40. ;
INFORMAT
F1 $CHAR10.
F2 BEST5.
UserName $CHAR16.
Domain $CHAR6.
F5 $CHAR12.
F6 BEST7.
RunMonth $CHAR12.
RunDate $CHAR2.
RunTime $CHAR40.
RunName $CHAR40. ;
INFILE WHLInst
LRECL=121
DLM='|'
truncover
DSD ;
input @ ;

INPUT
F1 : $CHAR10.
F2 : ?? BEST5.
UserName : $CHAR16.
Domain : $CHAR6.
F5 : $CHAR12.
F6 : ?? BEST7.
RunMonth : $CHAR12.
RunDate : $CHAR2.
RunTime : $CHAR40.
RunName : $CHAR40. ;
RUN;

Data OnlyDomainFolderlist(drop=Domain F1 F2 F5 F6 runmonth rundate);
*format RunTimeStamp datetime20.;
set work.folderlist;
RunTime = put(INTNX ('minute',input(RunTime,time.),-240),hhmm.);
where Domain = 'domain' and RunMonth="&RunMonth" and rundate="&RunDate" and UserName not like '7%';
RunTimeStamp= catx(trim(RunMonth),trim(Rundate),trim(RunTime));
WorkGroup='Retail';
run;

data _NULL_;
set OnlyDomainFolderlist;
call symputx(catx("_","MIPRUN",_N_),RunName);
call symputx("NRUN", _N_);
run;

%macro MIP_SUMMARY;
%do i = 1 %to &NRUN.;
%put &runInstances./&&MIPRUN_&i.;
%put &runInstances./&&MIPRUN_&i./logs;
libname mipout "&runInstances./&&MIPRUN_&i.";
libname miplog "&runInstances./&&MIPRUN_&i./logs";
data execution_log;
format RUNNAME $32.;
set mipout._execution_config_summary;
where NAME in ("RUN_INST_NUM_SIMULATION" "RUN_INST_HORIZON" "RUN_INST_POST_EXEC_PGRM");
RUNNAME = "&&MIPRUN_&i.";
keep RUNNAME NAME NUMERIC_VALUE TEXT_VALUE;
run;
data runstatus;
format RUNNAME ACTION $32.;
set miplog.runstatus;
where (TASK_NM = "RUN_EXECUTION" and ACTION_NM in ("start" "completed")) or
(TASK_NM = "POST_EXECUTION" and ACTION_NM = "completed");
RUNNAME = "&&MIPRUN_&i.";
ACTION = CATX("_", TASK_NM, ACTION_NM);
keep RUNNAME ACTION STEP_DTTM;
run;

%if &i = 1 %then %do;
data execution_all;
set execution_log;
run;
data runstatus_all;
set runstatus;
run;
%end;
%else %do;
data execution_all;
set execution_all execution_log;
run;
data runstatus_all;
set runstatus_all runstatus;
run;
%end;
%end;
proc sql;
create table execution_all as
select RUNNAME, NAME,
case when not missing(NUMERIC_VALUE) then strip(put(NUMERIC_VALUE, 32.))
else TEXT_VALUE
end as VALUE
from execution_all
;
quit;

proc sort data=execution_all;
by RUNNAME;
run;
proc sort data=runstatus_all;
by RUNNAME;
run;
proc transpose data=execution_all out=execution_out (drop=_NAME_);
var VALUE;
id NAME;
by RUNNAME;
run;
proc transpose data=runstatus_all out=runstatus_out (drop=_NAME_);
var STEP_DTTM;
id ACTION;
by RUNNAME;
run;
data totalruntime;
set runstatus_out;
keep RUNNAME EXECUTION_TIME TOTALTIME;
EXECUTION_TIME = ROUND((RUN_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01);
TOTALTIME = ROUND((POST_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01);
run;
%mend;
%MIP_SUMMARY;

proc sql;
create table retail_summary as
select A.*, B.RUN_INST_NUM_SIMULATION, B.RUN_INST_HORIZON, B.RUN_INST_POST_EXEC_PGRM, C.EXECUTION_TIME, C.TOTALTIME
from OnlyDomainFolderlist as A
left join execution_out as B on A.RUNNAME = B.RUNNAME
left join totalruntime as C on A.RUNNAME = C.RUNNAME
;
quit;

Data WHLOnlyDomainFolderlist(drop = Domain F1 F2 F5 F6);
*format RunTimeStamp datetime20.;
set work.WHLfolderlist;
where Domain = 'domain' and RunMonth="&RunMonth" and rundate="&RunDate" and UserName not like '7%';
RunTimeStamp= catx(trim(RunMonth),trim(Rundate),trim(RunTime));
WorkGroup='Wholesale';
run;

data _NULL_;
set WHLOnlyDomainFolderlist;
call symputx(catx("_","MIPRUN",_N_),RunName);
call symputx("NRUN", _N_);
run;

%macro WSL_MIP_SUMMARY;
%do i = 1 %to &NRUN.;
%put &WHLrunInstances./&&MIPRUN_&i.;
%put &WHLrunInstances./&&MIPRUN_&i./logs;
libname mipout "&WHLrunInstances./&&MIPRUN_&i.";
libname miplog "&WHLrunInstances./&&MIPRUN_&i./logs";
data execution_log;
format RUNNAME $32.;
set mipout._execution_config_summary;
where NAME in ("RUN_INST_NUM_SIMULATION" "RUN_INST_HORIZON" "RUN_INST_POST_EXEC_PGRM");
RUNNAME = "&&MIPRUN_&i.";
keep RUNNAME NAME NUMERIC_VALUE TEXT_VALUE;
run;
data runstatus;
format RUNNAME ACTION $32.;
set miplog.runstatus;
where (TASK_NM = "RUN_EXECUTION" and ACTION_NM in ("start" "completed")) or
(TASK_NM = "POST_EXECUTION" and ACTION_NM = "completed");
RUNNAME = "&&MIPRUN_&i.";
ACTION = CATX("_", TASK_NM, ACTION_NM);
keep RUNNAME ACTION STEP_DTTM;
run;

%if &i = 1 %then %do;
data execution_all;
set execution_log;
run;
data runstatus_all;
set runstatus;
run;
%end;
%else %do;
data execution_all;
set execution_all execution_log;
run;
data runstatus_all;
set runstatus_all runstatus;
run;
%end;
%end;
proc sql;
create table execution_all as
select RUNNAME, NAME,
case when not missing(NUMERIC_VALUE) then strip(put(NUMERIC_VALUE, 32.))
else TEXT_VALUE
end as VALUE
from execution_all
;
quit;

proc sort data=execution_all;
by RUNNAME;
run;
proc sort data=runstatus_all;
by RUNNAME;
run;
proc transpose data=execution_all out=execution_out (drop=_NAME_);
var VALUE;
id NAME;
by RUNNAME;
run;
proc transpose data=runstatus_all out=runstatus_out (drop=_NAME_);
var STEP_DTTM;
id ACTION;
by RUNNAME;
run;
data totalruntime;
set runstatus_out;
keep RUNNAME EXECUTION_TIME TOTALTIME;
EXECUTION_TIME = ROUND((RUN_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01);
TOTALTIME = ROUND((POST_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01);
run;
%mend;

%WSL_MIP_SUMMARY;

proc sql;
create table wholesale_summary as
select A.*, B.RUN_INST_NUM_SIMULATION, B.RUN_INST_HORIZON, B.RUN_INST_POST_EXEC_PGRM, C.EXECUTION_TIME, C.TOTALTIME
from WHLOnlyDomainFolderlist as A
left join execution_out as B on A.RUNNAME = B.RUNNAME
left join totalruntime as C on A.RUNNAME = C.RUNNAME
;
quit;

data mergedWHL_RTL_ALL (rename=(RUNTIME_NUM=RUNTIME));
format Env $5. workgroup $32. RUNTIME_NUM MMSS.;
set retail_summary wholesale_summary;
RUNTIME_NUM = SUBSTR(RUNTIME,1,2) * 60 + SUBSTR(RUNTIME, 4);
Env="Test";
drop RUNTIME RunDate RunMonth;
run;

proc sort data=mergedWHL_RTL_ALL;
by RUNTIME;
run;

/*data mergedWHL_RTL;*/
/*set work.WHLONLYDOMAINFOLDERLIST work.ONLYDOMAINFOLDERLIST;*/
/*environment="Dev";*/
/*run;*/
proc report data=work.mergedWHL_RTL_ALL;

run;

my report 

TestRetail2:18sweta.mukulkumarSM25CECL_CUBE_20190101_S9926Jun2:180  0.1.Env workgroup RUNTIME UserName RunName RunTimeStamp RUN_INST_NUM_SIMULATION RUN_INST_HORIZON RUN_INST_POST_EXEC_PGRM EXECUTION_TIME TOTALTIME

Test	Retail	2:19	sweta.mukulkumar	SM25CECL_ECL_20190101_S99	26Jun2:19	10	1	Phase2_Business_Banking	0.13	0.19
Test	Retail	11:51	paul.n.amaral	PA_AUTO_CUBE_201903v2	26Jun11:51	0			0.52	.
Test	Retail	12:31	paul.n.amaral	PA_ECL_AUTO_201903v2	26Jun12:31	100	96	Phase2_AUTO_CCAR_Balance_Adj	.	.
Test	Retail	13:25	anusha.jookuri	ZHLNS_CCAR_CUBE_20190401_S99	26Jun13:25	0			0.08	.
Test	Retail	13:25	anusha.jookuri	ZHLNS_CCAR_ECL_20190401_S99	26Jun13:25	1	32	Phase2_HELOAN_RECOVERY	0.11	.

my concern is i want to remove observation if i dont have execution time