05-03-2018 02:53 AM
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.
DD/MM/YYYY HH:MM AM/PRM
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
slices the first 10 characters to include double digit days of the month
returns blank with a dot
Any tips would be appreciated. Thanks
05-03-2018 04:10 AM
Well, to start with your data is not in yymmdd format is it, so thats why that format does not work.
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.
05-03-2018 10:41 AM
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.
05-03-2018 07:21 PM
05-03-2018 07:29 PM
05-03-2018 04:10 AM
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).
05-03-2018 07:54 PM
Various computed columns attempted and results shown above.
DT3 (output matches slicing via SUBSTR)