topic Re: Recoding dataset using an array in SAS Programming

Recoding dataset using an array

jkoonin — Fri, 10 May 2013 00:16:28 GMT

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!

Re: Recoding dataset using an array

Cynthia_sas — Fri, 10 May 2013 01:12:42 GMT

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

Re: Recoding dataset using an array

siliveri4uall — Fri, 10 May 2013 01:14:50 GMT

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;

Re: Recoding dataset using an array

Haikuo — Fri, 10 May 2013 03:26:08 GMT

@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

Re: Recoding dataset using an array

Ksharp — Fri, 10 May 2013 14:50:53 GMT

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