topic Re: Using DATA STEP to convert several obs per subject to one obs with several subjects in SAS Procedures

Using DATA STEP to convert several obs per subject to one obs with several subjects

chouchou — Thu, 09 Oct 2014 23:26:52 GMT

Hi guys, I just don't use proc transpose but using data steps, how to convert the data have -->data want?

Data have:

Obs Subj Score

1 001 7

2 001 6

3 001 5

4 001 5

5 001 4

6 002 8

7 002 7

8 002 6

9 002 6

10 002 6

Data want:

Obs Subj S1 S2 S3 S4 S5

1 001 7 6 5 5 4

2 002 8 7 6 6 6

3 003 8 7 6 6 5

I wrote the following codes:

proc sort data=one;

by Subj;

run;

data want;

set have;

by Subj;

array S{5};

do i=1 to 5;

retain S1-S5;

if first.Subj then call missing(of S1-S5);

S{i}=Score;

if last.Subj then output;

end;

run;

It comes out 3*5=15 observations, I guess the problem is raised by do loop, but how can I justify it?

Thank you so much.

Chouchou

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

stat_sas — Fri, 10 Oct 2014 00:06:31 GMT

proc sort data=have;
by Subj;
run;

data want;
do _n_=1 to 5;
   set have;
   by Subj;
   array S{5};
   S{_n_}=Score;
end;
run;

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

Reeza — Fri, 10 Oct 2014 03:17:35 GMT

Remove the do loop entirely but add another counter so you know what record your on for each id.

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

Tom — Fri, 10 Oct 2014 05:01:56 GMT

If you just logically step though your program you will see the logic error.

When it reads the first observation from HAVE it will execute the DO loop 5 times and copy the same value into all five variables in the array.

Then when it reads the next observation from HAVE it will do the same thing.

One simple pattern that can help with this is what is called a DOW loop. There are a number of papers you can read on it. Here is a recent one:

http://support.sas.com/resources/papers/proceedings14/1619-2014.pdf

It eliminates the need for the RETAIN and the CALL MISSING().

%let max=5 ;

data want ;

do i=1 by 1 until (last.subj);

set have ;

by subj ;

array s (&max);

s(i) = score;

end;

run;

You might want to add a step to pre-calculate the maximum size to use for your array.

proc sql noprint ;

select max(count) into :max separated by ' '

from (select subj,count(*) as count from have group by subj)

;

quit;

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

Loko — Fri, 10 Oct 2014 07:35:35 GMT

Hello,

Your code gives 15 observation in the output because you have the code:

if last.Subj then output;

inside the do loop therefore when the condition is true it writes 5 observations to the output , one for every step of do loop.

A number of solutions have been presented here therefore you have a choice

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

BrunoMueller — Fri, 10 Oct 2014 11:59:06 GMT

You can use the DATA Step loop to fill the array elements. To make it more dynamic you can find out first how many array elements you need. See sample code below:

data have;
  infile cards; 
  input
    Subj : 8.
    Score : 8.
  ;
  cards; 
001 7
001 6
001 5
001 5
001 4
002 8
002 7
002 6
002 6
; 

proc sql noprint; 
  select
    max( maxN ) into :maxN trimmed
  from (
    select
      subj
      , count(*) as maxN
    from
      have
    group by
      subj
      )
  ;
quit; 
%put NOTE: &=maxN;

data want;
  set have;
  by Subj;
  array S{&maxN};
  retain S1 - S&maxN;

  if first.Subj then do; 
    call missing(of S{*});
    i = 0; 
  end; 

  i + 1; 
  S{i} = Score;

  if last.Subj then do; 
    output; 
  end; 

  keep subj S1 - S&maxN;
run;

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

Ksharp — Fri, 10 Oct 2014 14:02:24 GMT

data have;
  infile cards; 
  input
    Subj : 8.
    Score : 8.
  ;
  cards; 
001 7
001 6
001 5
001 5
001 4
002 8
002 7
002 6
002 6
; 
run;
data have;
 set have;
 by subj;
 if first.subj then n=0;
 n+1;
 run;
proc sort data=have(keep=n) out=key nodupkey;by n;run;
data _null_;
 set key end=last;
 if _n_ eq 1 then call execute('data want;merge ');
 call execute(cats('have(rename=(Score=Score',n,') where=(n=',n,') )'));
 if last then call execute(';by Subj;drop n;run;');
run;

Xia Keshan

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

chouchou — Sat, 11 Oct 2014 00:32:07 GMT

The result is

Subj Score S1 S2 S3 S4 S5

001 4 7 6 5 5 4

just one obs, not two

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

stat_sas — Sat, 11 Oct 2014 01:03:11 GMT

Not sure, but I am getting two obs

Data have;
input Obs Subj Score;
datalines;
1      001 7
2      001 6
3      001 5
4      001 5
5      001 4
6      002 8
7      002 7
8      002 6
9      002 6
10     002 6
;

proc sort data=have;
by Subj;
run;

data want;
do _n_=1 to 5;
   set have;
   by Subj;
   array S{5};
   S{_n_}=Score;
end;
run;

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

chouchou — Sat, 11 Oct 2014 01:03:26 GMT

Hi loko, can you please explain me why it has both 4 and 5 for Subj001, while it only has 6 for Subj002? I would greatly appreciate your help.

Subj Score S1 S2 S3 S4 S5 i

001 4 4 5 5 5 5 1

001 4 4 4 5 5 5 2

001 4 4 4 4 5 5 3

001 4 4 4 4 4 5 4

001 4 4 4 4 4 4 5

002 6 6 6 6 6 6 1

002 6 6 6 6 6 6 2

002 6 6 6 6 6 6 3

002 6 6 6 6 6 6 4

002 6 6 6 6 6 6 5

Re: Using DATA STEP to convert several obs per subject to one obs with several subjects

chouchou — Sat, 11 Oct 2014 01:10:20 GMT

It's pretty weird. I just copied your code and it resolved to one obs.

:smileyconfused: