Desktop productivity for business analysts and programmers

QueryBuilder > Advanced Expression (Formatting char to date)

Reply
New Contributor
Posts: 4

QueryBuilder > Advanced Expression (Formatting char to date)

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

Super User
Super User
Posts: 9,840

Re: QueryBuilder > Advanced Expression (Formatting char to date)

Posted in reply to jason_tran

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.

Trusted Advisor
Posts: 2,127

Re: QueryBuilder > Advanced Expression (Formatting char to date)

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.

New Contributor
Posts: 4

Re: QueryBuilder > Advanced Expression (Formatting char to date)

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
New Contributor
Posts: 4

Re: QueryBuilder > Advanced Expression (Formatting char to date)

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 (.)
Super User
Posts: 5,917

Re: QueryBuilder > Advanced Expression (Formatting char to date)

Posted in reply to jason_tran

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).

Data never sleeps
New Contributor
Posts: 4

Re: QueryBuilder > Advanced Expression (Formatting char to date)

Posted in reply to jason_tran

image.png

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.)

Ask a Question
Discussion stats
  • 6 replies
  • 147 views
  • 0 likes
  • 4 in conversation