Hi,
I would like to seperate out a column of times (e.g., 9:00, 12:00, 16:00) to two columns of times (AM and PM) but I need to keep the associated variables. I have additional columns of data (date and movement rates) that I need to still be associated with the new AM and PM columns. For example, I need to go from:
Date | Time | Rate |
---|---|---|
01/01/11 | 07:23 | 48 |
01/01/11 | 12:34 | 127 |
01/02/11 | 18:56 | 29 |
to:
Date | AM Time | PM Time | AM Rate | PM Rate |
---|---|---|---|---|
01/01/11 | 07:23 | 12:34 | 48 | 127 |
01/02/11 | 18:56 | 29 | ||
01/03/11 |
Does anyone know if this is possible is SAS?
Much thanks.
Then how about:
proc format;
value ampm
0-43199='AM'
other='PM'
;
run;
data have;
informat date mmddyy8.;
format date mmddyy8.;
informat time time5.;
input Date Time Rate;
cards;
01/01/11 07:23 48
01/01/11 07:24 47
01/01/11 07:25 46
01/01/11 07:25 45
01/01/11 12:34 127
01/01/11 12:35 128
01/01/11 12:36 129
01/02/11 18:56 29
;
proc sql;
create table am as
select date, time as am_time,
rate as am_rate,
put(time,ampm.) as ampm
from have
group by date, calculated ampm
having rate=max(rate) and
time le '11:59't
;
create table pm as
select date, time as pm_time,
rate as pm_rate,
put(time,ampm.) as ampm
from have
group by date, calculated ampm
having rate=max(rate) and
time gt '11:59't
;
quit;
data want (drop=ampm);
merge am pm;
format am_time pm_time time5.;
by date;
run;
How do you know to match up the first two obs? only because they come after each other?
But yes its possible, generally called a transpose and if you're doing more than one variable a data step transpose is a good place to start googling.
Yes, so I actually have up to 10 different times (with associated rate) for each day. I need to extract the AM time for the maximium rate and the PM time for the maximum rate. Originally I used proc rank to rank the rates so that I had a sorted (descending order) list of rates with another column of associated times. With the transpose function, I could then generate columns that had the times of the maximum rates (Rate1 column was a list of times with all maximum movement rate, Rate2 column was a list of times with the second highest movement rate, etc...) for each day. The problem is that the columns (Rate1, Rate2...) are a mix of AM and PM times and I need them seperated out. I tried starting from the beginning again, but I am unable to figure out how to transpose and seperate AM and PM at the same time.
Then how about:
proc format;
value ampm
0-43199='AM'
other='PM'
;
run;
data have;
informat date mmddyy8.;
format date mmddyy8.;
informat time time5.;
input Date Time Rate;
cards;
01/01/11 07:23 48
01/01/11 07:24 47
01/01/11 07:25 46
01/01/11 07:25 45
01/01/11 12:34 127
01/01/11 12:35 128
01/01/11 12:36 129
01/02/11 18:56 29
;
proc sql;
create table am as
select date, time as am_time,
rate as am_rate,
put(time,ampm.) as ampm
from have
group by date, calculated ampm
having rate=max(rate) and
time le '11:59't
;
create table pm as
select date, time as pm_time,
rate as pm_rate,
put(time,ampm.) as ampm
from have
group by date, calculated ampm
having rate=max(rate) and
time gt '11:59't
;
quit;
data want (drop=ampm);
merge am pm;
format am_time pm_time time5.;
by date;
run;
awesome!!! i had another variable in there that i didn't mention and was losing 2,000+ days of data. I was able to add it in under the select and group fields and all looks great. Thank you so much!
As long as you only have one set of am/pm times per day, or one of each, then you could use something like:
proc format;
value ampm
0-43199='AM'
other='PM'
;
run;
data have;
informat date mmddyy8.;
format date mmddyy8.;
informat time time5.;
input Date Time Rate;
cards;
01/01/11 07:23 48
01/01/11 12:34 127
01/02/11 18:56 29
;
data want (keep=date am: pm:);
set have;
by date;
format am_time pm_time time5.;
retain am_time am_rate;
format am_time pm_time time5.;
if first.date then do;
call missing(of am:);
call missing(of pm:);
end;
if put(time,ampm.) eq 'AM' then do;
am_time=time;
am_rate=rate;
end;
else do;
pm_time=time;
pm_rate=rate;
end;
if last.date then output;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.