BookmarkSubscribeRSS Feed
aarony
Obsidian | Level 7

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?

6 REPLIES 6
Chrishi
Calcite | Level 5

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

aarony
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Tom
Super User Tom
Super User

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;

Chrishi
Calcite | Level 5

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

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1261 views
  • 1 like
  • 4 in conversation