topic Re: How to modify data in sas eg in SAS Enterprise Guide

How to modify data in sas eg

sugita — Thu, 21 Jun 2012 06:11:03 GMT

Hi,

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

basically i have this data

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'

Re: How to modify data in sas eg

Alpay — Thu, 21 Jun 2012 08:30:30 GMT

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.

Re: How to modify data in sas eg

sugita — Fri, 22 Jun 2012 05:15:52 GMT

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.

Re: How to modify data in sas eg

Alpay — Fri, 22 Jun 2012 05:52:30 GMT

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;

Re: How to modify data in sas eg

sugita — Fri, 22 Jun 2012 06:21:33 GMT

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.

Re: How to modify data in sas eg

shivas — Fri, 22 Jun 2012 06:39:31 GMT

Hi..

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

Thanks,

Shiva

Re: How to modify data in sas eg

Alpay — Fri, 22 Jun 2012 07:00:14 GMT

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;

Re: How to modify data in sas eg

sugita — Fri, 22 Jun 2012 07:22:50 GMT

Hi,

No good. I tried to change the format

and this is what i get

Re: How to modify data in sas eg

TomKari — Fri, 22 Jun 2012 11:45:13 GMT

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)

Re: How to modify data in sas eg

Patrick — Fri, 22 Jun 2012 12:07:40 GMT

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