Recoding dataset using an array

[jkoonin]

Hi folks,

  Suppose I have a data set consisting of scores for two subjects in student ID order, e.g.

English 100 200 300 400 500 600 700  (real data much longer...)

Math 400 500 400 500 400 500 400

I would like to use an array (or other method) to recode this data set to look like this:

1 100 400

2 200 500

3 300 400

4 400 500

5 500 400

6 600 500

7 700 400

so that I can run a paired t-test.

Wondering if someone could let me know how to do this?

Many thanks!

[Cynthia_sas]

Hi,

Let's assume that your dataset is called WORK.WIDE and the variables are called COURSE and S1-S7. Like this:

Course   S1  S2  S3  S4  S5  S6  S7  

English 100 200 300 400 500 600 700

Math    400 500 400 500 400 500 400

Instead of using arrarys, I would use PROC TRANSPOSE.

cynthia

proc transpose data=wide out=long name=Student;

id course;

var s1-s7;

run;

ods listing;

proc print data=long;

run;

The output from the above looks like this in the LISTING window:

       Obs    Student    English    Math

        1       s1         100       400

        2       s2         200       500

        3       s3         300       400

        4       s4         400       500

        5       s5         500       400

        6       s6         600       500

        7       s7         700       400

[siliveri4uall]

data marks;

input subject $ A101-A107;

cards;

English 100 200 300 400 500 600 700

Maths 400 500 400 550 400 500 250

Science 450 200 350 500 620 450 300

;

run;

data mark_col;

set marks;

array demo{7} A101-A107;

do i=1 to 7;

studentid=Vname(mark(i));

marks=mark(i);

output;

end;

drop A101-A107 i;

run;

/* alternative Method using proc transpose */

proc sort data=marks;

by subject;

run;

proc transpose data=marks out=mark_col1(rename=(_name_=studentid col1=marks));

var A101-A107;

by subject;

run;

[Haikuo]

@siliveri4uall,

If forced to use Array(), then you will have to dump everything into memory (temp array or hash) before you can rearrange them and put them back in the wanted order:

data marks;

input subject $ A101-A107;

cards;

English 100 200 300 400 500 600 700

Maths 400 500 400 550 400 500 250

Science 450 200 350 500 620 450 300

;

run;

/*if have to use array, such as a homework requirement*/

proc sql noprint;

  select nobs into :nobs from dictionary.tables where libname='WORK' AND MEMNAME='MARKS';

  SELECT NVAR-1 INTO :NVAR FROM DICTIONARY.TABLES where libname='WORK' AND MEMNAME='MARKS';

  select cats(subject) into :vname separated by ' ' from marks;

  select quote(cats(name)) into :_id separated by ', ' from dictionary.columns where libname='WORK' AND MEMNAME='MARKS' and UPCASE(NAME) NE 'SUBJECT';

  select cats(name)into :id separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='MARKS' and UPCASE(NAME) NE 'SUBJECT';

QUIT;

/*2x dimension array()*/

data want_array;

  array t(&nobs,&nvar) _temporary_;

  ARRAY sid(&nvar) $ _temporary_ (&_id);

  set marks end=last;

  array id(&nvar) &id;

  array class(&nobs) &vname;

   do i=1 to &nvar;

    t(_n_,i)=id(i);

   end; 

   if last then do i=1 to 7;

       do _n_=1 to &nobs;

     class(_n_)=t(_n_,i);

       end;

        student_id=sid(i);

        output;

   end;

   keep STUDENT_ID &vname;

run;

/*Hash*/

proc sql noprint;

select cats(subject) into :_vname separated by ' ' from marks;

  select cats(subject) into :vname separated by ', ' from marks;

    select quote(cats(subject)) into :qvname separated by ',' from marks;

  select cats(name)into :id separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='MARKS' and UPCASE(NAME) NE 'SUBJECT';

   select quote(cats(name)) into :_id separated by ', ' from dictionary.columns where libname='WORK' AND MEMNAME='MARKS' and UPCASE(NAME) NE 'SUBJECT';

  select nobs into :nobs from dictionary.tables where libname='WORK' AND MEMNAME='MARKS';

quit;

data _null_;

  if _n_=1 then do;

    call missing (&vname);

    length student_id $ 8;

  declare hash h(ordered: 'a');

  h.definekey('student_id');

  h.definedata("student_id", &qvname);

  h.definedone();

end;

set marks end=last;

   array id(&nvar) &id;

   array v(&nobs) &_vname;

   ARRAY sid(&nvar) $ _temporary_ (&_id);

    do i=1 to dim(sid);

   student_id=sid(i);

   rc=h.find(); v(_n_)=id(i);

   rc=h.replace();

  end;

if last then rc=h.output(dataset:'want_hash');

run;

Haikuo

[Ksharp]

Here is for Array .

data marks;
input subject $ A101-A107;
cards;
English 100 200 300 400 500 600 700
Maths 400 500 400 550 400 500 250
;
run;
data _null_;
 set marks end=last;
 array x{7,2} _temporary_;
 array s{7} a:;
 do i=1 to 7;
  x{i,_n_}=     s{i};
 end;
if last then do;
 do i=1 to 7;
   do j=1 to 2;
    put x{i,j}=     @;
   end;
    put / ;
 end;
end;
run;

Ksharp