Help using Base SAS procedures

seperating am and pm times - keeping associated variables

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

seperating am and pm times - keeping associated variables

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:

DateTimeRate
01/01/1107:2348
01/01/1112:34127
01/02/1118:5629

to:

DateAM TimePM TimeAM RatePM Rate
01/01/1107:2312:3448127
01/02/1118:5629
01/03/11

Does anyone know if this is possible is SAS?

Much thanks.


Accepted Solutions
Solution
‎02-17-2012 08:34 PM
PROC Star
Posts: 7,364

seperating am and pm times - keeping associated variables

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;

View solution in original post


All Replies
Super User
Posts: 17,912

seperating am and pm times - keeping associated variables

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.

New Contributor
Posts: 3

seperating am and pm times - keeping associated variables

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.

Solution
‎02-17-2012 08:34 PM
PROC Star
Posts: 7,364

seperating am and pm times - keeping associated variables

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;

New Contributor
Posts: 3

seperating am and pm times - keeping associated variables

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!

PROC Star
Posts: 7,364

seperating am and pm times - keeping associated variables

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: pmSmiley Happy;

  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 amSmiley Happy;

    call missing(of pmSmiley Happy;

  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;

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 209 views
  • 0 likes
  • 3 in conversation