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,487

seperating am and pm times - keeping associated variables

Posted in reply to Salsalito

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: 19,851

seperating am and pm times - keeping associated variables

Posted in reply to Salsalito

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,487

seperating am and pm times - keeping associated variables

Posted in reply to Salsalito

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,487

seperating am and pm times - keeping associated variables

Posted in reply to Salsalito

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 and locked.

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

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