BookmarkSubscribeRSS Feed
jkoonin
Calcite | Level 5

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!

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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
Onyx | Level 15

@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
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 880 views
  • 0 likes
  • 5 in conversation