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;
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!
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.