Solved
Contributor
Posts: 46

counts

hi all,

i have the below table :

 class Mark1 Mark2 Mark3 Mark4 Mark5 Mark6 Mark7 Mark8 Mark9 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

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

 mark class1 class2 class3 class4 class5 class6 class7 1 1 1 1 0 1 1 0 2 1 1 1 1 1 1 0 3 1 1 1 1 1 1 1 4 1 1 1 1 0 0 1 5 0 0 0 1 0 1 1 6 1 0 1 1 1 0 1 7 1 1 1 1 0 1 1 8 1 1 0 0 1 0 1 9 1 1 0 0 1 1 0

any ideas on this will be great.

Thanks ...

Accepted Solutions
Solution
‎04-19-2015 04:25 AM
Super User
Posts: 10,770

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

All Replies
Solution
‎04-19-2015 04:25 AM
Super User
Posts: 10,770

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

Posts: 3,167

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 and locked.