topic Re: Converting many rows of data into one in SAS Procedures

Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 15:05:51 GMT

Hi all, I'm hoping you can help me with my data issue. I have data with tens of thousands of rows per participant (repeated measurements

), and I need to convert it into a single row. I would appreciate any suggestions! I have read the information on transposing, but that's not quite what I want to do.

A data file for one participant is in a format like this (but obviously has many more rows):

Time VariableA VariableB VariableC

1 3 2 9

2 8 5 11

3 2 7 5

And I need to convert it to a format like this, retaining the timestamp for each set of measurements as its own variable:

Time1 VariableA1 VariableB1 VariableC1 Time2 VariableA2 VariableB2 VariableC2 Time3 VariableA3 VariableB3 VariableC3

1 3 2 9 2 8 5 11 3 2 7 5

I have many files like this, and want to eventually end up with a single file that has one row per participant.

Re: Converting many rows of data into one

kewong — Thu, 16 Oct 2014 15:16:21 GMT

This SAS macro has helped me achieved what you want to do. One caveat is to have a unique identifier for each participant and transpose by that variable.

SAS macro %MultiTranspose: Transposing multiple variables in a SAS data set within a single macro call

Re: Converting many rows of data into one

RW9 — Thu, 16 Oct 2014 15:27:25 GMT

Hi,

My first question would be why? You will end up with a lot of variables and few rows which probably isn't the best for processing or further work. What is it your trying to achieve as there may be better ways of getting the outcome.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 15:49:41 GMT

Re: kewong. Thank you, I will try this out. This may be a silly question, but where do I put that line of %multtransponse? Does it go within a data statement? And what about the huge amount of code that is at the bottom of the website you linked to... do I need all of that, or just the one line?

Re: RW9, I need to sum the observations across time variables. If it helps, what I'm working with is physical activity data collected over an entire week. Each 15-second interval is its own row. So I need to create a summary variable for each day that adds up all of the physical activity. But also will need to mark and pull out the hours of the day when the person was sleeping (they were still wearing the monitor 24 hrs/day, even when asleep, so there are data from when they are rolling around in bed, etc., which I need to remove).

Re: Converting many rows of data into one

bootstrap_armada — Thu, 16 Oct 2014 15:51:43 GMT

I think you probably can achieve the data structure you want by transposing - that's where I'd be focusing my efforts. You'll need to do multiple transpositions into new datasets - one for each variable or variable, timestamp combination, AND you'll need something as a unique key to merge datasets back together - perhaps a concatenated field. Once you've transposed all the variables you'll need to join or merge the wide datasets back together to get one dataset - that's where you'll be glad you when through the effort to create a key. See the link below for step-by-step details.

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

Re: Converting many rows of data into one

RickAster — Thu, 16 Oct 2014 16:01:46 GMT

The sample data shows a perfectly normal transpose. Is there something else that makes it different?

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 16:03:56 GMT

I think that may be my error in understanding the term. Sometimes in Excel I use the transpose feature, which switches columns and rows. So I assumed that proc transpose would reverse columns and rows, not turn multiple rows into a single row...

Re: Converting many rows of data into one

Julian1 — Thu, 16 Oct 2014 16:04:21 GMT

I actually agree with mr. boostrap.

you can basically use proc transpose, but you need a unique key say "Account" to eventually merge all columns. Change in new variable names can be achieved by a macro fairly easily.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 16:11:03 GMT

Thank you everyone who has responded so far. This is my first time trying the forum so I didn't know whether anyone would chime in. I really appreciate this help.

So, in re: Bootstrap_armada, I read the example you sent. That example seems a bit more straightforward because each family has data for the same three years: 96, 97, and 98. So they specify "id=year" and the new columns are labeled accordingly But will that still work for my example, when the values for the "time" variable are different for everyone? (Everyone started wearing their monitor on a different date)? I need mine not to append the column name with a value, but basically just say "time1 time2 time3 time4" etc. because "time1" is different for every person.

** Edited to add that other than that bit, the page you sent is very helpful and I was able to understand what they're doing... so it seems promising... it's just the "id" piece I'm unsure about.

Re: Converting many rows of data into one

Julian1 — Thu, 16 Oct 2014 16:27:09 GMT

if i understand correctly, you can sort by time, then use a first. function to create a count, then you have 1,2,3,4. by string function here you go, time1 time2 time3 time4...etc

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

Re: Converting many rows of data into one

ballardw — Thu, 16 Oct 2014 16:31:05 GMT

I think you might be better off creating a group variable from time. Since your "time" is obviously not time but occurrence if you describe the relation to the desired totals needed I'll bet we can do this with your existing data and proc means/summary.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 16:44:49 GMT

Ballard, I edited the original post to add one of my actual datasets in case that is helpful to anyone. I think I understand what you are saying about the time variable... if I understand correctly, you are suggesting that we could just enumerate the occurences. Since everyone has 1 week of data from their physical activity sensor ( ~40,320 15-second intervals of data), we could just number those 40,320 occurences, right, rather than labeling them by the actual date/time of the occurence? The rub is that I need to remove blocks of data... so if someone slept from 11:21 pm - 6:07 a.m. between the first and second day, I need to be able to identify and delete all the rows that pertain to those dates and times.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 16:50:10 GMT

So, I ran a single-variable PROC TRANSPOSE, which seems to have worked fine. The log said I have 1 observation and 40,320 variables, so that sounds good to me. But I can't really take a look at the data, because when I run PROC CONTENTS, SAS gets all gummed up and stops responding. Is this normal with huge datasets? How do you work with it if you can't look at it?

Re: Converting many rows of data into one

RickAster — Thu, 16 Oct 2014 16:56:54 GMT

This is the kind of problem you can run into, not with large data sets, but with such a large number of variables. It explains why commenters are so reluctant to create so many variables.

Re: Converting many rows of data into one

RW9 — Thu, 16 Oct 2014 17:02:34 GMT

So your really going to try working with a dataset which has 40320 variables? Best of luck with that. Me personally I would get your data looking like (normalised):

Time VariableName VariableResult

1 A 3

1 B 2

1 C 9

2 A 8

2 B 5

2 C 11

...

Then use grouped functions, e.g for SQL:

proc sql;

create table SUMS as

select distinct TIME,

sum(VARIABLE_RESULT) as SUM

from DATASET

group by TIME;

quit;

If you could provide actual test data and required output I could have a look at it.

Re: Converting many rows of data into one

optimist — Thu, 16 Oct 2014 17:09:40 GMT

Hastie,

Could I ask what sort of analysis or report you plan on running with the resultant dataset? As others have commented, 40,320 variable is a lot of variables. If you will be doing additional analysis what statistical/analytical application (SAS, Stata, SPSS, R, etc.) and which procedure or routine do you plan on using? If you are preparing a report, what tool do you intend to use (Excel, Crystal Reports, SQL Server, etc.) Someone here might be able to suggest a more efficient way to organize the data.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 17:24:20 GMT

Sure. So, I have 7 days of data per person, collected 24 hrs/day in 15-sec intervals, and as the data are organized currently, each 15-second interval is its own row. Eventually I would to have like a dataset that looks like this, where I have already removed all of the data for the chunks of time when I know (based on separate logs) that each person was sleeping.

Participant WearDay1 SittingHrs1 StandHrs1 SteppingHrs1 SitStandTransitions1 Steps1 WearDay2 SittingHrs2 StandHrs2...

Guy Smiley 3/4/2014 9.1 2.6 1.8 41 5596 3/5/2014 9.6 2.1

Kermit 11/21/2013 8.6 4.1 2.2 68 7637 11/22/2014 8.8 3.2

PrairieDawn 5/9/2014 9.9 1.9 1.3 32 2974 5/10/2014 8.7 1.5

Then I would create overall variables for each person's week, so that I know the average hrs/day each person spends sitting down, standing up, and stepping, as well as the average number of steps/day they take, and the average number of sit-to-stand transitions they make.

*** EDited this to fix a couple of variable names.

Re: Converting many rows of data into one

Hatsie — Thu, 16 Oct 2014 17:31:01 GMT

And, Optimist, to answer the rest of your question, I am planning to do the rest of the analysis in SAS. I work in public health and this is data from a pilot study we did looking at lifestyle patterns in a specific disease group.... the eventual goal is to quantify/describe how they are spending their time, and determine whether those who are spending more time sitting have higher levels of certain biomarkers in the blood.

Re: Converting many rows of data into one

SpiggyTopes — Fri, 17 Oct 2014 19:01:58 GMT

I suspect you're just making it hard for yourself by converting rows to columns, both in your initial steps and in your eventual requirement. In terms of the latter, what does having all days on a single row give you that one row per day would not? A simple PROC SUMMARY would allow you to roll together days of data if that's what's ultimately required. If it's the kind of project I think it is, then there will be further questions and analyses that spring from the results of this stage, and the simpler your data structure is, the easier it's going to be to perform those analyses. Keep everything in simple non-repeating columns as far as you possibly can, and I think you can probably achieve that using simple DATA steps, PROC SORTs and PROC SUMMARYs. Transpose for reporting at the end if you must, but keep your data simple as possible as long as possible.

Re: Converting many rows of data into one

Ksharp — Sat, 18 Oct 2014 14:21:32 GMT

The simplest way is using proc means + idgroup. But you need to reorder variables . The second way is you don't need to reorder them .

data have;
input id Time VariableA VariableB VariableC     ;
cards;
1 1        3               2              9
1 2        8               5             11
1 3        2               7              5
2 1        3               2              9
2 2        8               5             11
2 3        3               2              9
2 4        8               5             11
;
run;

/*** First way  Need reorder variables***/
proc sql noprint;
select max(n) into : n from (select count(*)as n from have group by id);
quit;
%put &n;
proc summary data=have;
by id;
output out=want(drop=_:) idgroup(out[&n] (Time VariableA VariableB VariableC) = );
run;

/** Second way No need reorder variables**/
proc sort data=have(keep=time) out=temp nodupkey;by time;run;
data _null_;
 set temp end=last;
 if _n_ eq 1 then call execute('data want1;merge ');
 call execute(catt('have(where=(time_',time,'=',time,') rename=(Time=Time_',time,' VariableA=VariableA_',time,' VariableC=VariableC_',time,'))'));
 if last then call execute(';by id;run;');
run;

Xia Keshan