BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sugita
Calcite | Level 5

Hi,

I was wondering if i can do this in sas eg.

basically i have this data

screen 2.jpg

i was wondering if there is a way in sas eg so that in coloumn F16 I can put a space between the 'hour' and the 'AM/PM'

So, in the first row

it would be like  '20/06/2012 10:02:35 AM'

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

Use the query builder, create a query on your dataset. Create a new advanced expression, and in the advanced expression editor, type

substr(F16, 1, 19) || ' ' || substr(F16, 20, 2)

View solution in original post

9 REPLIES 9
Alpay
Fluorite | Level 6

You can define you own format by using directives in a picture statement as follows:

proc format;

  picture dmyhmsampm low-high = '%0d/%0m/%Y %0I:%0M:%0S %p' (datatype=datetime);

run;

data _null_;

  x = '20Jun2012 10:02:35'dt;

  put x dmyhmsampm.;

run;

For explanation of these directives please see the documentation for PICTURE statement in PROC FORMAT section.

sugita
Calcite | Level 5

Hi, I am sorry but i dont understand at all everything that you just said in your post.

Do i filter the data? or query? or what?

I got that data from a csv file that i import to the SAS Data imported just like in the pic.

Untitled.jpg

Alpay
Fluorite | Level 6

SAS assigns a format to a column to display the value stored in that column.

The above code would be used with datetime values which are stored as numeric variables.

I just realized that F16 is a character column not numeric.

In this case, you will need to change the value of that string with an operation or create a new variable.

NewVar = PRXCHANGE('s/(AM|PM)/ $1/',-1,F16);

Example:

data _null_;

  x = '20/06/2012 10:02:35AM';

  y = PRXCHANGE('s/(AM|PM)/ $1/',-1,x);

  put y=;

run;

sugita
Calcite | Level 5

I still do not understand your post.

Yes the F16 coloumn is stored in string. but where do i write this code?

All I did so far is just import the data from csv and i dont see the option of putting the code.

shivas
Pyrite | Level 9

Hi..

Try this...hope it helps..you can also modify informat/format according to your requirement.eg.png

Thanks,

Shiva

sugita
Calcite | Level 5

Hi,

No good. I tried to change the format

Untitled.jpg

and this is what i get

2.jpg

Patrick
Opal | Level 21

What you should do is convert this datetime string in your raw data into a SAS datetime value. The challenge you have is that the way your datetime string looks like is kind of unusual: AM/PM is a US American way of writing a date - but then the datepart of your string is DMY and not MDY. There is no SAS ready made informat to convert such a string into a SAS datetime value (which is a numeric value containing the number of seconds since 1/1/1960).

A 2 step approach is needed: First read the data as string, then use the query builder and in the advanced expression builder convert the string to a form as interpretable by a standard SAS Informat  (mmddyyyy hh:mm:ss AM|PM), then read this string using a informat which will convert the string to a SAS datetime value.

You then also need to apply a format to the output variable in order to make the numeric value (the seconds since 1/1/1960) human readable. The format could be "Datetime21."

If you need the formated datetime value to look exactly as you specified it (ddmmyyyyy hh:mm:ss AM|PM) then again there is no standart SAS Format for this. You would need to define your own format using the code Alpay provided (the picture format). You would need to run this code in a code node before you then can use it in the query builder.

Below the nested function creating a SAS datetime value out of your raw data datetime string - to be used in the query builder advanced expression:

input(prxchange('s/(\d{2}\/)(\d{2}\/)(.*)(AM|PM)/$2$1$3 $4/oi',1,t1.F16),mdyampm25.2)

And last but not least:

The huge advantage in converting the string to a SAS datetime value is that you then can use calender functions with it (like intnx(), intck(), datepart()....).

Alpay
Fluorite | Level 6

Once you get your data set say work.state you can open a program editor window and create another data set.

File -> New -> Program

data work.state2;

  set work.state;

  NewVar = PRXCHANGE('s/(AM|PM)/ $1/',-1,F16);

run;

TomKari
Onyx | Level 15

Use the query builder, create a query on your dataset. Create a new advanced expression, and in the advanced expression editor, type

substr(F16, 1, 19) || ' ' || substr(F16, 20, 2)

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
  • 9 replies
  • 3183 views
  • 1 like
  • 5 in conversation