topic Re: Need help to convert character Datetime into 01MMMyy in SAS Programming

Need help to convert character Datetime into 01MMMyy

WorkingMan — Wed, 03 Oct 2018 04:49:35 GMT

Hi,

I am seeking for help to convert the actual sas DATETIME into 01MmmYY

For example:

I want my result to be like

01Jan06

01Dec17

Note: Day is always 01 regardless of the actual date.

My original value is in datetime format below:

1-Jan-2015 8:00

Can it be done in one line of statement? It will be put in "WHERE" clause so i do not want to create a new variable.

Initially, my source data is always in Jan2017 Feb2017 Mar2017 format. However, there are 4 letters month recently and i have to change my logic.

My original where clause filter is as below:

where put(input(cats('01',scan(file_source,1,'.')),date9.), yymmn6.) eq "&Period";

However, I am now taking another column that is character datetime: 1-Jan-2015 8:00

I want my result to be always

01Jan06

01Dec17

My updated but not working code for that is as below

where put(input(cats('01',scan(scan(scan(lt_date,1,' '),-2,'-'),1,'-'),scan(scan(scan(lt_date,1,' '),-2,'-'),-1,'-20')))) eq "&Period"

The error the 2nd last or 3rd last closing bracket and expecting an EQ.

Suggestions and advice are very much appreciated.

Re: Need help to convert character Datetime into 01MMMyy

ChrisNZ — Wed, 03 Oct 2018 05:49:20 GMT

Not too sure I undertand where the issue is , but his works:

data t;
  c='1-Jan-2015 8:00';
  if catt('01',scan(c,2),scan(c,3)) ='01Jan2015' then putlog 'Correct';
run;

Re: Need help to convert character Datetime into 01MMMyy

Kurt_Bremser — Wed, 03 Oct 2018 06:45:50 GMT

Start by giving us a usable example of your "source data".

If it's a dataset, use the macro from my second footnote and convert it to a data step, which you then can post according to the hints in my third footnote.

If it is in an external file, post a sample of that external file, either as attachment, or by copy-pasting it into a window opened with the {i} button.

Re: Need help to convert character Datetime into 01MMMyy

RW9 — Wed, 03 Oct 2018 07:35:19 GMT

Your post is very confusing and contradictory. Please start by posting some example test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

This will illustrate what you actually have - that way we can see if your data is dates or characters etc. and avoids the contradictions, for example you say "

My original value is in datetime format below:

1-Jan-2015 8:00

" - however then proceed to show code which is concatenating 01 to it which indicates that is not your value at all.

Next up show what you want out from that test data. For instance, explain (if it is character as seems to be the case from some of what you post) why the date is kept as character - which is a very unusable format for dates/times. Reading in the data, or first process should be to convert dates and times to numeric actual dates and times so they are easy to work with, and before you say "but they need to look like mmyyy" - this is what formats are for, a numeric date variable can be fomatted in many ways without changing the underlying data.

In terms of the final lines of your post, why do you want mixed case month? I don't recall a SAS format which will do that for you, however if you really have to then simply:

want=put(have,date9.);
substr(want,4,2)=lowcase(substr(want,4,2));

Or create your own picture format.

Re: Need help to convert character Datetime into 01MMMyy

WorkingMan — Wed, 03 Oct 2018 11:02:47 GMT

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_&period;
		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.

Re: Need help to convert character Datetime into 01MMMyy

Kurt_Bremser — Wed, 03 Oct 2018 11:11:39 GMT

At the end of the first data step, create a new variable with the correctly formatted date, then add the following code:

if newvar = "&period.";
drop newvar;

Re: Need help to convert character Datetime into 01MMMyy

ballardw — Wed, 03 Oct 2018 16:14:02 GMT

Anytime you want to compare dates, times or datetimes create a SAS date, time or datetime valued numeric. You will save yourself a LOT of time and headache in the long run.