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
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.
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.
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).
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.)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.