counts

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

counts

hi all,

i have the below table :

classMark1Mark2Mark3Mark4Mark5Mark6Mark7Mark8Mark9
class1146927836
class2448342791
class3446724413
class4642453437
class5898228136
class6931273752
class7368564347

and the table i am trying to create is  the below:

so what it does is returns 1 if a mark appears in variables Mark1- mark9 and stores against that class.

ex: value 1 appeared only once in class1 then it returned 1 , where as value 6 appeared twice in class 1 , it has to return 1

markclass1class2class3class4class5class6class7
11110110
21111110
31111111
41111001
50001011
61011101
71111011
81100101
91100110

any ideas on this will be great.

Thanks ...


Accepted Solutions
Solution
‎04-19-2015 04:25 AM
Grand Advisor
Posts: 9,578

Re: counts

data have;
input class $     Mark1     Mark2     Mark3     Mark4     Mark5     Mark6     Mark7     Mark8     Mark9 ;
cards;
class1     1     4     6     9     2     7     8     3     6
class2     4     4     8     3     4     2     7     9     1
class3     4     4     6     7     2     4     4     1     3
class4     6     4     2     4     5     3     4     3     7
class5     8     9     8     2     2     8     1     3     6
class6     9     3     1     2     7     3     7     5     2
class7     3     6     8     5     6     4     3     4     7
;
run;
proc transpose data=have out=temp(drop=_name_);
by class;
run;
proc sort data=temp nodupkey;by col1 class;run;
data temp;
 set temp;
 retain flag 1;
run;
proc transpose data=temp out=temp1(drop=_name_);
by col1;
id class;
var flag;
run;
proc stdize data=temp1 out=want reponly missing=0;run;

Xia Keshan

View solution in original post


All Replies
Solution
‎04-19-2015 04:25 AM
Grand Advisor
Posts: 9,578

Re: counts

data have;
input class $     Mark1     Mark2     Mark3     Mark4     Mark5     Mark6     Mark7     Mark8     Mark9 ;
cards;
class1     1     4     6     9     2     7     8     3     6
class2     4     4     8     3     4     2     7     9     1
class3     4     4     6     7     2     4     4     1     3
class4     6     4     2     4     5     3     4     3     7
class5     8     9     8     2     2     8     1     3     6
class6     9     3     1     2     7     3     7     5     2
class7     3     6     8     5     6     4     3     4     7
;
run;
proc transpose data=have out=temp(drop=_name_);
by class;
run;
proc sort data=temp nodupkey;by col1 class;run;
data temp;
 set temp;
 retain flag 1;
run;
proc transpose data=temp out=temp1(drop=_name_);
by col1;
id class;
var flag;
run;
proc stdize data=temp1 out=want reponly missing=0;run;

Xia Keshan

Respected Advisor
Posts: 3,124

Re: counts

Another approach, actually original idea was from Keshan as well,

data have;

     input class $     Mark1 Mark2     Mark3     Mark4 Mark5     Mark6     Mark7 Mark8     Mark9;

     cards;

class1     1 4     6     9 2     7     8 3     6

class2     4 4     8     3 4     2     7 9     1

class3     4 4     6     7 2     4     4 1     3

class4     6 4     2     4 5     3     4 3     7

class5     8 9     8     2 2     8     1 3     6

class6     9 3     1     2 7     3     7 5     2

class7     3 6     8     5 6     4     3 4     7

;

run;

PROC SQL;

     SELECT DISTINCT CLASS INTO :CLASS SEPARATED BY ' ' FROM HAVE;

     SELECT DISTINCT QUOTE(TRIM(CLASS)) INTO :QCLASS SEPARATED BY ',' FROM HAVE;

     ;

DATA _NULL_;

     IF _N_=1 THEN

           DO;

                RETAIN ZERO 0;

                DECLARE HASH H(ORDERED:'A');

                H.DEFINEKEY("MARK");

                H.DEFINEDATA("MARK",&QCLASS.);

                H.DEFINEDONE();

           END;

     SET HAVE END=LAST;

     ARRAY MK(*) MARK:;

     ARRAY CLS(*) &CLASS.;

     DO I=1 TO DIM(MK);

call pokelong (repeat(put (0,rb8.),6), addrlong(CLS[1]), 56);

           MARK=MK(I);

           RC=H.FIND();

           CLS(COMPRESS(CLASS,,'KD'))=1;

           RC=H.REPLACE();

     END;

     IF LAST THEN

           RC=H.OUTPUT(DATASET:'WANT');

RUN;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 232 views
  • 5 likes
  • 3 in conversation