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

hi team,

in the past i had a similar question and Tom &others helped me .

That time it was transposing the below "have" dataset into a single record. But now i want the below data to transposed into WANT dataset  having the 5 records like shown!!!

Thanks

/*aFTER SORTING MY DATASET BY :iD AND TIME I GET THE BELOW

"have"*/

ID      flag                        time         
101      TIMEIN     04SEP89:07:30:00   
101      TIMEIN     04SEP89:13:45:00  
101      TIMEOUT    05SEP89:07:15:00    
101      TIMEIN     21SEP89:17:55:00 
101      TIMEOUT    22SEP89:06:00:00
101      TIMEOUT    23SEP89:06:00:00
101      TIMEIN     24SEP89:06:00:00

/*WANT*/

ID                 timein                       timeout       
101           04SEP89:07:30:00          .  
101           04SEP89:13:45:00    05SEP89:07:15:00
101           21SEP89:17:55:00    22SEP89:06:00:00
101                 .                           23SEP89:06:00:00
101           24SEP89:06:00:00           .

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

Seems like the main issue is how to group the in/out so that you can transpose.  This is what I came up with but it may not work for anything but your test data.

data stay;
   input ID $  flag $ time :datetime.;
  
format time datetime.;
  
cards;
101      TIMEIN     04SEP89:07:30:00   
101      TIMEIN     04SEP89:13:45:00  
101      TIMEOUT    05SEP89:07:15:00    
101      TIMEIN     21SEP89:17:55:00 
101      TIMEOUT    22SEP89:06:00:00
101      TIMEOUT    23SEP89:06:00:00
101      TIMEIN     24SEP89:06:00:00
;;;;
  run;
proc print;
 
run;
data stay2;
   set stay;
   by id flag notsorted;
  
if flag eq 'TIMEIN' then stay+1;
  
if not first.flag and flag eq 'TIMEOUT' then stay+1;
  
run;
proc print;
  
run;
proc transpose out=stay3;
   by id stay;
   id flag;
   var time;
   run;
proc print;
  
run;

View solution in original post

3 REPLIES 3
data_null__
Jade | Level 19

Seems like the main issue is how to group the in/out so that you can transpose.  This is what I came up with but it may not work for anything but your test data.

data stay;
   input ID $  flag $ time :datetime.;
  
format time datetime.;
  
cards;
101      TIMEIN     04SEP89:07:30:00   
101      TIMEIN     04SEP89:13:45:00  
101      TIMEOUT    05SEP89:07:15:00    
101      TIMEIN     21SEP89:17:55:00 
101      TIMEOUT    22SEP89:06:00:00
101      TIMEOUT    23SEP89:06:00:00
101      TIMEIN     24SEP89:06:00:00
;;;;
  run;
proc print;
 
run;
data stay2;
   set stay;
   by id flag notsorted;
  
if flag eq 'TIMEIN' then stay+1;
  
if not first.flag and flag eq 'TIMEOUT' then stay+1;
  
run;
proc print;
  
run;
proc transpose out=stay3;
   by id stay;
   id flag;
   var time;
   run;
proc print;
  
run;
robertrao
Quartz | Level 8

Thanks for the reply data_null_.

But why would it not work out of my test data?

Thanks

data_null__
Jade | Level 19

I said it MAY not.

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
  • 3 replies
  • 696 views
  • 1 like
  • 2 in conversation