BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Salsalito
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
Reeza
Super User

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.

Salsalito
Calcite | Level 5

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.

art297
Opal | Level 21

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;

Salsalito
Calcite | Level 5

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!

art297
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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