Help using Base SAS procedures

Transpose using array

Reply
Frequent Contributor
Posts: 102

Transpose using array

Hi I have dataset:

year

name

one_str_a

one_str_b

one_str_c

one_con_a

one_con_b

one_con_c

two_str_a

two_str_b

two_str_c

two_con_a

two_con_b

two_con_c

1991

john

2

1

1

1

1

2

1

1

1

1

2

1

1991

amy

1

0

0

0

0

1

0

0

0

0

1

0

1991

ted

2

0

1

0

1

2

1

0

1

1

2

1

1991

tom

1

2

0

2

0

1

0

2

0

0

1

0

1992

john

0

1

0

1

0

0

0

1

0

0

0

0

1992

amy

0

2

2

2

2

0

2

2

2

2

0

2

1992

ted

2

1

1

1

1

2

1

1

1

1

2

1

1992

tom

1

2

2

2

2

1

2

2

2

2

1

2

my years are from 1991 to 2012 and i have more observations than the above but i just wanted to give you a picture. my scores range from 0~2.

I want it in this form:

varname

Score

1991

john

one_str_a

2

1991

john

one_str_b

1

1991

john

one_str_c

1

1991

john

one_con_a

1

1991

john

one_con_b

1

1991

john

one_con_c

2

1991

john

two_str_a

1

1991

john

two_str_b

1

1991

john

two_str_c

1

1991

john

two_con_a

1

1991

john

two_con_b

2

1991

john

two_con_c

1

Could you kindly provde me of your guidance?

Contributor
Posts: 29

Re: Transpose using array

Hope we can transpose this one normally by proc transpose.

proc transpose  data=dataset out=final;

var  one_str_a one_str_b one_str_c one_con_a one_one_b one_one_c two_str_a two_str_b two_str_c etc...;

by year name;

run;

Sudeer

Frequent Contributor
Posts: 102

Re: Transpose using array

hi sudeer, thank you much. can u shed some light on how to apply array to this as well? thanks again!

Super User
Super User
Posts: 7,407

Re: Transpose using array

Hi,

Why would you want to use arrays?  I have seen that people use them when going from normalized data to transposed data to have more control over the output, but arrays don't make much sense when going from transposed to normalized data.  If you really want to do it another way then the below code is a suggestion:

proc sql;
  create table WORK.LOOP as
  select  distinct NAME,TYPE
  from    SASHELP.VCOLUMN
  where   LIBNAME="SASHELP"
    and   MEMNAME="CLASS"
    and   NAME ne "Name";
quit;

data _null_;
  set work.loop end=last;
  if _n_=1 then call execute('data want (keep=name section result);
                                set sashelp.class;
                                attrib section result format=$200.;');
  if type="num" then call execute(' section="'||strip(name)||'"; result=strip(put('||strip(name)||',best.)); output;');   
  if type="char" then call execute(' section="'||strip(name)||'"; result='||strip(name)||'; output;');   
  if last then call execute('run;');
run;

Super User
Super User
Posts: 6,502

Re: Transpose using array

You can use arrays to normalize this type of data.  It is much easier in SAS if you use numeric suffixes for your blocks of variables.

To make this program easier I just assumed that you wanted to transpose all of the numeric variables except for YEAR.

data have ;

  input year name $

        one_str_a one_str_b one_str_c one_con_a one_con_b one_con_c

        two_str_a two_str_b two_str_c two_con_a two_con_b two_con_c

  ;

cards;

1991 john 2 1 1 1 1 2 1 1 1 1 2 1

1991 amy 1 0 0 0 0 1 0 0 0 0 1 0

run;

data want ;

set have ;

length varname $32 score 8 ;

keep year name varname score ;

array scores _numeric_ ;

do i=1 to dim(scores);

    score = scores(i);

    varname = vname(scores(i));

    if upcase(varname) ne 'YEAR' then output;

end;

run;

Contributor
Posts: 29

Re: Transpose using array

Hi tom above code works fine except we need one addition, as you have created score variable, i think if statement should include 'SCORE' as score is also transposed, which results in extra observations.

if upcase(varname) not in ('YEAR' 'SCORE') then output;

Sudeer

Super User
Super User
Posts: 6,502

Re: Transpose using array

Or just move the ARRAY statement to before the LENGTH statement that defines SCORE.

Ask a Question
Discussion stats
  • 6 replies
  • 257 views
  • 1 like
  • 4 in conversation