BookmarkSubscribeRSS Feed
jason_tran
Calcite | Level 5

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Calcite | Level 5
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
Calcite | Level 5
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
Tourmaline | Level 20

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
jason_tran
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3649 views
  • 0 likes
  • 4 in conversation