topic Re: How to transfer multiple rows to single row per id_record? in SAS Programming

How to transfer multiple rows to single row per id_record?

bhr-q — Sat, 14 Sep 2024 03:49:03 GMT

Hello all,

Many thanks to help me to transfer the below design to the expected design.

record_id event Q1…….. Q24

1 base

1 pre_arm

1 post_arm

2 base

2 pre_arm

2 post_arm

20 base

20 pre_arm

20 post_arm

Expected design:

record_id event b_Q1……..b_Q24 event pre_Q1……..pre_Q24 event post_Q1……..post_Q24

1 base pre_arm post_arm

I think it is not only proc transpose, not sure.

Thank you in advance

Re: How to transfer multiple rows to single row per id_record?

Ksharp — Sat, 14 Sep 2024 06:19:57 GMT

data have;
input record_id           event     $    Q1 $  Q2 $;                    
cards;
1                           base        1     1                                                   
1                           pre_arm      2     2                                        
1                           post_arm     3  3
2                          base          3  3
2                          pre_arm        2     2                       
2                          post_arm       1   1
;

proc sql noprint;
select distinct catt('have(where=(record_id=',record_id,' and event_',event,'="',event,'")
 rename=(event=event_',event,' Q1=',event,'_Q1 Q2=',event,'_Q2))') into :merge separated by ' '
 from have;
quit;
data want;
 merge &merge.;
 by record_id;
run;

Re: How to transfer multiple rows to single row per id_record?

PaigeMiller — Sat, 14 Sep 2024 11:11:20 GMT

Data in long form is almost always preferable to data in wide format. Preferable because almost all SAS PROCs are designed to work on long data sets. So, how to do this? Don't do it, not only is that easier but you will be better off. Maxim 19.

What possible benefit is there of this wide format that you want compared to the long format? What is the next step in your work, is it some sort of data analysis, plotting, graphings, creating a table? Please tell us in detail so we can best advise you.

Re: How to transfer multiple rows to single row per id_record?

Tom — Sat, 14 Sep 2024 12:02:29 GMT

PROC TRANSPOSE will work. You might need to run it twice however.

proc transpose data=have out=tall;
  by id event;
  var q1-q24;
run;
proc transpose data=tall out=want(drop=_name_);
  by id;
  id event _name_;
  var col1;
run;

Or you could leave the data as it is and just PRINT it that way using PROC REPORT.

proc report data=have;
   column id event,(q1-q4);
   define id / group;
   define event / across;
run;

Re: How to transfer multiple rows to single row per id_record?

Kurt_Bremser — Sat, 14 Sep 2024 16:09:30 GMT

I'd go one step further and make your initial dataset longer:

proc transpose data=have out=long name=quarter;
by record_id event notsorted;
var q:;
run;

proc report data=long;
column record_id event,quarter,col1;
define record_id / group;
define event / "" across;
define quarter / "" across;
define col1 / "" analysis;
run;

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Sun, 15 Sep 2024 03:00:35 GMT

Thank you for your help; this is a survey questionnaire with a Likert scale. I wanted to run the Wilcoxson sign rank test using proc univariate to do a few comparisons, such as comparing pre_arm with post_arm.

In the first design, if I create a column named total, which is the Sum(of Q1-Q24) for each patient_id, then how do I calculate (total score of post_arm – total score of pre_arm) for each id to place that in proc univariate?

but with the second design, I can create two columns named a total of pre_arm, which is Sum(of preQ1-preQ24), and a total of post_arm, which is Sum(of postQ1-postQ24), then calculate the difference b/n these measures to place that in proc univariate.

It seems that there would be easier way with long format, is n’t it?

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Sun, 15 Sep 2024 03:02:03 GMT

Thank you for your answer, it was helpful.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Sun, 15 Sep 2024 03:02:36 GMT

Thank you for your answer, it was helpful

Re: How to transfer multiple rows to single row per id_record?

Kurt_Bremser — Sun, 15 Sep 2024 13:05:45 GMT

From the long dataset I proposed, you need one query:

proc sql;
create table want as
  select
    record_id,
    sum(case when event="pre_arm" then col1 else 0 end) as pre_sum,
    sum(case when event = "post_arm" then col1 else 0 end) as post_sum,
    calculated post_sum - calculated pre_sum as diff
  from long
  group by record_id
;
quit;

Re: How to transfer multiple rows to single row per id_record?

ballardw — Sun, 15 Sep 2024 21:02:36 GMT

Note that the example provided in the online documentation of Proc NPAR1WAY for an exact two-sample test uses a long data set with the Class variable using the two levels.

So to perform such a test on your example data;

data have;
input record_id           event     $    Q1   Q2 ;                    
cards;
1                           base        1     1                                                   
1                           pre_arm      2     2                                        
1                           post_arm     3  3
2                          base          3  3
2                          pre_arm        2     2                       
2                          post_arm       1   1
;

proc npar1way data=have;
   where event in ('pre_arm' 'post_arm');
   class event;
   var q1 q2;
   exact wilcoxon;
run;

The WHERE statement reduces it to two levels, as stated in your desire.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Sun, 15 Sep 2024 21:10:22 GMT

Thanks so much for your help.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Sun, 15 Sep 2024 21:35:13 GMT

Thanks for your answer, I think before doing any stat test, even proc npar1way, need to calculate the total score in my first design, and I created that like

data have;
set have;
if  event = "pre_arm" then  total_w=Sum(of &Q1-Q24);
if  event= "post_arm" then  total_g=Sum(of &Q1-Q24);
run;

then, there would be two columns like the below, due to this missing, proc npar1way doesn’t work here

event total_w total_g

pre_arm 2 .

post_arm . 4

pre_arm 3 .

Re: How to transfer multiple rows to single row per id_record?

Tom — Sun, 15 Sep 2024 23:54:17 GMT

@bhr-q wrote:

Thanks for your answer, I think before doing any stat test, even proc npar1way, need to calculate the total score in my first design, and I created that like
data have;
set have;
if  event = "pre_arm" then  total_w=Sum(of &Q1-Q24);
if  event= "post_arm" then  total_g=Sum(of &Q1-Q24);
run;
then, there would be two columns like the below, due to this missing, proc npar1way doesn’t work here

event     total_w   total_g

pre_arm 2            .

post_arm          .            4

pre_arm 3 .

.

.

.

Not sure what you are saying but if you just make ONE variable with the total it should work.

data want;
  set have;
  total=sum(of Q1-Q24);
run;

Do not overwrite your input dataset (you might lose information and have to remake if you made a logic error).

The & in your code should not be there. That would cause the macro processor to look for a macro variable named Q1 to expand before passing the resulting statement on to SAS to add to the data step.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Mon, 16 Sep 2024 00:56:27 GMT

One variable is correct, but the proc npar1way that you proposed needs two variables, and I believe proc npar1way doesn't work here.

Re: How to transfer multiple rows to single row per id_record?

Tom — Mon, 16 Sep 2024 02:54:52 GMT

@bhr-q wrote:

One variable is correct, but the proc npar1way that you proposed needs two variables, and I believe proc npar1way doesn't work here.

Huh. You have two variables. TOTAL is the numeric variable to use the VAR statement. The other is variable that has two values to use the CLASS statement.

https://www.stat.purdue.edu/~tqin/system101/method/method_wilcoxon_rank_sum_sas.htm

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Mon, 16 Sep 2024 03:02:55 GMT

I know I have two variables, but with the first design, as I show above , proc npar1way doesn't work here.

my design is a Wilcoxson sign rank test not Wilcoxson rank sum test.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Mon, 16 Sep 2024 03:16:19 GMT

You proposed a great way, using proc transpose twice, and you also proposed there would be another way, which is using the first design in my first comment and going for proc npar1way, but the latter is not possible.

Re: How to transfer multiple rows to single row per id_record?

PaigeMiller — Mon, 16 Sep 2024 12:26:47 GMT

@bhr-q wrote:

Thank you for your help; this is a survey questionnaire with a Likert scale. I wanted to run the Wilcoxson sign rank test using proc univariate to do a few comparisons, such as comparing pre_arm with post_arm.

In the first design, if I create a column named total, which is the Sum(of Q1-Q24) for each patient_id, then how do I calculate (total score of post_arm – total score of pre_arm) for each id to place that in proc univariate?

but with the second design, I can create two columns named a total of pre_arm, which is Sum(of preQ1-preQ24), and a total of post_arm, which is Sum(of postQ1-postQ24), then calculate the difference b/n these measures to place that in proc univariate.

It seems that there would be easier way with long format, is n’t it?

This is good information. It should be in your original post, otherwise we have to guess why you are doing this confusing transformation. Without knowing this, we can't provide good answers. Anyway, @Kurt_Bremser (and possibly others) have shown how to do this with long data set, and no one could have provided the way to do this with the information in your original post.

Re: How to transfer multiple rows to single row per id_record?

bhr-q — Mon, 16 Sep 2024 12:18:10 GMT

Thanks all of you for your help, much appreciated.