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'
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)
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.
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.
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;
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.
Hi..
Try this...hope it helps..you can also modify informat/format according to your requirement.
Thanks,
Shiva
Hi,
No good. I tried to change the format
and this is what i get
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()....).
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;
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)
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.