topic Need Help to arrange data without Proc transpose in SAS Procedures

Need Help to arrange data without Proc transpose

PuneetSingh — Thu, 10 Jul 2014 10:47:08 GMT

My data is in this shape:

1 amit 36 physics

1 nitu 28 chemistry

1 ketu 28 biology

2 amit 98

2 nitu 78

2 ketu 28

3 amit 74

3 nitu 93

3 ketu 92

I want to transpose it to below shape without using proc transpose.

the output should look like

1 amit 36 98 74

2 nitu 28 78 93

3 ketu 28 28 92

I don't want to use any hard coding in this e.g. in arrays statement i don't want to mention the subject names physics chemistry biology and array elements should also be computed in macros only

Thanks in advance

Re: Need Help to arrange data without Proc transpose

RW9 — Thu, 10 Jul 2014 10:55:06 GMT

Hi,

I had some questions, firstly why do you not want to use a function designed specifically to do the task you are requesting to do? The only other real option is arrays. You could generate the required array parameters:

proc sql;

select max(col1)

into :NUMOPTS

from HAVE;

quit;

proc sort data=have;

by name col1;

run;

data want;

set have;

by name;

array results{&NUMOPTS.} best.;

if first.name then id=1;

else id=id+1;

results{I}=col3;

if last.name then output;

run;

Re: Need Help to arrange data without Proc transpose

PuneetSingh — Thu, 10 Jul 2014 11:47:24 GMT

thanks for your prompt reply,

i wrote

results{} = col3;

if last.name then output;

run;

gave wrong results.

as i am not able to understand

results{I}=col3;

{I}whats this please?

Re: Need Help to arrange data without Proc transpose

RW9 — Thu, 10 Jul 2014 12:05:06 GMT

I is an incrementor. So the first result you want to put in {1} slot 1 in your array, then next result to slot{2} etc. we use the variable I and add one to that each time.

Re: Need Help to arrange data without Proc transpose

Tom — Thu, 10 Jul 2014 12:57:43 GMT

Assuming that the first column indicates the order that you want the values in the array for that student then you could do this, but PROC TRANSPOSE would do the exact same thing a lot easier.

data have ;

infile cards truncover ;

input index name $ result class $20.;

cards;

1 amit 36 physics

1 nitu 28 chemistry

1 ketu 28 biology

2 amit 98

2 nitu 78

2 ketu 28

3 amit 74

3 nitu 93

3 ketu 92

run;

proc sort;

by name index ;

run;

proc sql noprint ;

select max(index) into :max from have;

quit;

data want ;

if 0 then set have (keep=name);

array score(&max);

do until(last.name);

set have;

by name;

score(index)=result;

end;

drop index result class ;

run;

Re: Need Help to arrange data without Proc transpose

PuneetSingh — Fri, 11 Jul 2014 06:46:11 GMT

Hey Tom,

Thanks so much for this, works wonderfully for my problem.

Though i wasn't able to understand the following:

1. two set statements and

2. score(index)=result;

3, if 0 then set (is it refering to _n_?

thanks so much for the help and if i can understand the way code is executing it would be awesome help. thanks in advance.

Re: Need Help to arrange data without Proc transpose

Tom — Fri, 11 Jul 2014 11:28:19 GMT

The extra set statement is a method to insure that NAME is define first. The condition 0 is always false so it is never executed, but SAS will define the variables referenced in the set statement. It is not actually needed, but if you remove it then you should move the ARRAY statement until after the real SET statement to prevent the new SCORExx variables from appearing in the data set before the NAME variable.

SCORE(INDEX)=RESULT is just a normal assignment statement using an array reference. The variable INDEX is used as the index into the array. So if INDEX=1 then the value from RESULT will be placed into SCORE1. Note that because the SET statement is nested inside of the DO loop we do not need to retain the values of the variables SCORE1-SCORE3 that make up the array because we read all of the records from the source in a single iteration of the data step. It also means that we do not have to clear them when we start and new NAME as the data step iteration will clear them.

data want ;

do until(last.name);

set have;

by name;

array score(&max);

score(index)=result;

end;

drop index result class ;

run;

Re: Need Help to arrange data without Proc transpose

Ksharp — Fri, 11 Jul 2014 13:58:04 GMT

Try this one. Or if you are interested about tranposing data by DATA STEP. Refer to the paper of Arthur.T and me .

http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

data have ;
  infile cards truncover ;
  input index name $ result class $20.;
cards;
1 amit 36  physics
1 nitu 28  chemistry
1 ketu 28 biology
2 amit 98 physics
2 nitu 78 chemistry
2 ketu 28 biology
3 amit 74 physics
3 nitu 93 chemistry
3 ketu 92 biology
;
run;

proc sql;
select distinct catt('have(where=(index=',index,' and  class="',class,'") rename=(result=_',index,'))')  into : list separated by ' ' from have;
quit;
data want(drop=class index);
 merge &list ;
 by name class;
 n+1;
run;

Xia Keshan