topic seperating am and pm times - keeping associated variables in SAS Procedures

seperating am and pm times - keeping associated variables

Salsalito — Sat, 18 Feb 2012 00:36:50 GMT

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.

seperating am and pm times - keeping associated variables

Reeza — Sat, 18 Feb 2012 00:44:50 GMT

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.

seperating am and pm times - keeping associated variables

art297 — Sat, 18 Feb 2012 01:07:12 GMT

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;

seperating am and pm times - keeping associated variables

Salsalito — Sat, 18 Feb 2012 01:12:11 GMT

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.

seperating am and pm times - keeping associated variables

art297 — Sat, 18 Feb 2012 01:34:55 GMT

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;

seperating am and pm times - keeping associated variables

Salsalito — Sat, 18 Feb 2012 02:24:54 GMT

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!