How to create variable based on minutes in a certain level by ID

[marksanter]

Hello,

I have a dataset where I need to create minutes in separate levels by ID. The levels are 1-7 and time is structured in 30 second intervals. I essentially need new variables to capture the amount of time in mins spent in each level. I have attached example datasets of what I have and what I would like the data to look like. 

Can someone help me figure out a code to run this?

Thank you for your help.

[PaigeMiller]

Many of us refuse to download Excel files because they are a security risk. The proper way to provide data is as SAS data step code (instructions).

Also:

I essentially need new variables to capture the amount of time in mins spent in each level.

Does this mean you want the SUM of minutes spent in each level?

[marksanter]

Yes, that is correct. And I apologize, I will provide the SAS data step code.

[Reeza]

What are the rules to go from Have to Want? 
Can you take the first few rows and show the results?
For example for duration you need a start and end time but you only have one time so how is the duration calculated? 

I'm also assuming that all your times are for the same day, otherwise, if the date flips how do you know?

---

@marksanter wrote:

Hello,

I have a dataset where I need to create minutes in separate levels by ID. The levels are 1-7 and time is structured in 30 second intervals. I essentially need new variables to capture the amount of time in mins spent in each level. I have attached example datasets of what I have and what I would like the data to look like. 

Can someone help me figure out a code to run this?

Thank you for your help.

---

[marksanter]

Hello,

The times are all for one continuous period. The date does not really have an important place here. Essentially, I would need a code that looks through the stage column, and for example, if there were 10 7s listed for an ID, MINS_in_7 would 5 since it is binned in 30 sec increments.

The time variable shows 30 second increments. 

Thank you for your help!

[Reeza]

proc freq data=have noprint;
table id*level/ out=durations sparse missing;
run;

data want;
set durations;
time_in_period = count/2;
run;

*transpose to desired structure;

• Assume you mean by period instead of level (level has 8 categories 0-7, Period has 7, P1-P7). SInce you keep saying it has 7, I assume you're referring to Period not level. 
• See if the above gives you the answers you want. If so let us know and we can show you how to transpose and remerge it back into the main data set if you really want the structure you've shown.