Desktop productivity for business analysts and programmers

How to modify data in sas eg

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to modify data in sas eg

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'


Accepted Solutions
Solution
‎06-22-2012 07:45 AM
Trusted Advisor
Posts: 1,058

Re: How to modify data in sas eg

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


All Replies
Frequent Contributor
Posts: 95

Re: How to modify data in sas eg

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.

Occasional Contributor
Posts: 15

Re: How to modify data in sas eg

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

Frequent Contributor
Posts: 95

Re: How to modify data in sas eg

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;

Occasional Contributor
Posts: 15

Re: How to modify data in sas eg

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.

Super Contributor
Posts: 349

Re: How to modify data in sas eg

Hi..

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

Thanks,

Shiva

Occasional Contributor
Posts: 15

Re: How to modify data in sas eg

Hi,

No good. I tried to change the format

Untitled.jpg

and this is what i get

2.jpg

Respected Advisor
Posts: 3,837

Re: How to modify data in sas eg

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

Frequent Contributor
Posts: 95

Re: How to modify data in sas eg

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;

Solution
‎06-22-2012 07:45 AM
Trusted Advisor
Posts: 1,058

Re: How to modify data in sas eg

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)

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 365 views
  • 1 like
  • 5 in conversation