Hi everyone.
Sorry for the confusion.
Let me explain in detailed.
Initially, I have a data step that has a hash object statement in it to do table lookup. However, one of the source columns has been changed, which it provides the month and year in character format. So for now, I have to pick another column which stores the datetime in character type.
The data looks like that:
1-May-2015 10:00:00
13-Jun-2015 01:30:00
and etc.
data first;
length lf_date $30
infile DATALINES dsd missover;
input lf_date;
cards;
1/5/2015 10:00
31/5/2015 10:00
13/6/2015 1:30
18/12/2017 1:30
run;
In my data step, i previously had a where clause to filter out data that is not needed. Initially, i had this where clause:
where put(input(cats('01',scan(file_source,1,'.')),date9.), yymmn6.) eq "&Period";
the data in file_source looks like below:
Jan17.csv
Feb17.csv
The where clause above works perfectly as it will first scan and get the data before the 1st dot. Then, Concat 01,Jan17 tgt and format it with date9 in input statement which converts the data into numeric. Then, convert the value into yymmn6 which will produce the result like below:
201805
201603
Variable Period is assigned before the starts of data step. Lets assume we hardcode Period=201805 in this case.
Notice the variable "Period" in the where clause. The value of Period is always in the format of 201805 or 201402 and etc.
Until recently, file_source column has been removed and all i can use is a column(lt_date) that has the value of below :
1-May-2015 10:00:00
13-Jun-2015 01:30:00
I have tried with all kind of possible combination of scan substr and etc to extract the data from the above format into 201805 format in a single where clause statement without having to create a new variable as a new variable will require a new datastep for the filtering(meaning the new variable cannot be used within the same datastep or else it will throw error "column not found in set table".
For now, I have below codes written which i think is not efficient as I have to create a new datastep to do the where clause filter:
if lt_date ne '-' then do;
removedtime=substr(scan(lt_date,-1,'-'),1,4);
removedday=scan(lt_date,-2,'-');
mthyr=put(input(cats('01',removedday,removedtime),date9.),yymmn6.);
output dm.ncp_load_tnbt_.
end;
as you all can see, mthyr is newly created in the if statement above. I cannot put statement below within the same "if" block.
where mthyr = "&Period";
Hence, in my script, I have 2 datastep.
1st data step:
- read from source table, and include hash object lookup statement.
- Include IF statement above to assign into new column called newyr
2nd data step:
- read from 1st data step
- apply where clause to filter the period.
What i want to do is to eliminate the 2nd data step, and perform everything within the first data step. However, up to this point, i cannot think of a better way to do so. If anyone has a better solution, I would be more than happier to know.
