QueryBuilder > Advanced Expression (Formatting char to date)

[jason_tran]

Hi all,

Quite new to SAS EG previously using Alteryx for my data cleansing and totally lost trying to do a simple data type conversion. Due to the point and click nature of the process flow I'd rather pick up how to do the following via Querybuilder as opposed to coding via Program.

I have a data extracted from Salesforce where a Date field which looks as such but is read as character after importing the data via CSV.

DATE

DD/MM/YYYY HH:MM AM/PRM

eg

2/04/2018 11:58 AM

10/04/2018 3:36 PM

What I want to do is the following to compile some time series insights into the data

- convert the character type of DATE to a date readable by SAS EG. (yymmdd10,)

- determine week number from the year; WEEK(DATE,7.)

- group weeks with Saturday as the start of the week eg 31st March to 6th April would be considered a week.; INTCK/INTNX i believe

I've attempted the following

- SUBSTR(t1.DATE,1,10) 

slices the first 10 characters to include double digit days of the month

- INPUT(t1.DATE,yymmdd10,)

returns blank with a dot

Any tips would be appreciated. Thanks

[RW9]

Well, to start with your data is not in yymmdd format is it, so thats why that format does not work.

input(scan(t1.data,1," "),anydtdte.)

Should work, i.e. take first word up to space, then convert to number - note I don't code all in shouting.

What I would say secondly however is that you should really fix the import step, get the import to read the data correctly, post processing is nethier necessary nor an efficient way of proceeding.

[Doc_Duke]

Jason,

To follow-up:  RW9's comment "What I would say secondly however is that you should really fix the import step" is the best was to address this import issue.  Just apply the anydtdte. informat to the .CSV file date-time and it will bring it in as a SAS date-time variable.  Then you can create your derived versions in the query-builder.

[jason_tran]

Just tried manually assigned Date/Time as the type via modifying the import data process but it doesnt recognize and returns . (dot) values for every row

[jason_tran]

This worked for half of the dates.
Any single date values; 1,2,3,4 etc returns the SAS readable date however any date from the 10th return the dot (.)

[LinusH]

input() interprets the string, so the format here should match the source, hence DDMMYY. should be used.

Then you can assign YYMMDD. format to the new variable for display purposes (not necessary to make intnx() or weekday() functions to work).

[jason_tran]

Various computed columns attempted and results shown above.

DT

SUBSTRI(t1.data,1,10)

DT2

INPUT(SCAN(t1.date,1," "),ANYDTDTE.)

DT3 (output matches slicing via SUBSTR)

SCAN(t1.date,1," ")

DT4

INPUT(SCAN(t1.date,1" "),DDMMYYw.)

DT5

INPUT(SUBSTR(t1.date,1,10),DDMMYYw.)

DT6

PUT(INPUT(SCAN(t1.date,1" "),DDMMYYw.)YYMMDD10.)