BookmarkSubscribeRSS Feed
soham_sas
Quartz | Level 8

Hi 

 

i have a data set like below , i want to count the number of One (1) present in each variable 

 

Data have;
infile cards dlm= '09'x;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 1 0 1 0
0 0 1 1 1 0 0
1 1 0 0 0 0 0
0 0 0 0 1 1 1
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 0 0 1 0
0 0 0 0 0 0 0
1 0 0 0 0 1 0
0 0 1 1 0 0 0

;
run;

 

 

Final output should be like below 

 

Var_namecount
Var13
Var23
Var35
Var45
Var54
Var67
Var74
11 REPLIES 11
ballardw
Super User

If the variable values are all ones and zeroes then the easiest might be to sum them:

 

You do not say whether you need a data set or a report.

 

Proc means data=have sum maxdec=0;

   var var1-var7;

run;

soham_sas
Quartz | Level 8

Hi , i want to create a data set out of it to be used in next step 

 

and the values are other than 0 and 1 also in the real time data 

PaigeMiller
Diamond | Level 26

@soham_sas wrote:

and the values are other than 0 and 1 also in the real time data 


 

This is an important detail that should have been mentioned in the original post, as we can only go by the example you give us, and we can't write code for other data that is different than the example you give.

 

In this case, where the values can be other than 0 or 1, you can use PROC FREQ, or do this counting of values that equal 1 in a data step.

 

UNTESTED CODE

 

data want;
    set have end=eof;
    array v var1-var7;
    array c count1-count7;
    do i=1 to 7; 
         if v(i)=1 then c(i)+1;
    end;
    if eof then output;
    keep var1-var7;
run;

 

--
Paige Miller
novinosrin
Tourmaline | Level 20
Data have;
infile cards truncover ;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 1 0 1 0
0 0 1 1 1 0 0
1 1 0 0 0 0 0
0 0 0 0 1 1 1
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 0 0 1 0
0 0 0 0 0 0 0
1 0 0 0 0 1 0
0 0 1 1 0 0 0

;
run;

Proc means data=have  maxdec=0;

   var var1-var7;
output out=temp(drop=_:) sum=/autoname;
run;

proc transpose data=temp out=want;
run;
novinosrin
Tourmaline | Level 20

@soham_sas wrote:

and the values are other than 0 and 1 also in the real time data 

 

Oh well, filter your dataset with a where condition and make the proc means solution work. Yes, it will take one more step but very easy to follow.

Amir
PROC Star

Hi,

 

Borrowing the array definitions from @PaigeMiller, you could try the following:

 

data want(keep = var_name count);
   array v var1-var7;
   array c count1-count7;

   do until(last_obs);
      set have end = last_obs;
      
      do i=1 to 7;
         c[i] + v[i] = 1;
      end;
   end;

   do i=1 to 7;
      var_name = cats('Var',i);
      count    = c[i];
      output;
   end;
run;

 

 

Regards,

Amir.

PaigeMiller
Diamond | Level 26

@Amir

I'm afraid I don't understand this step or see how it helps:

 

c[i] + v[i] = 1;

 

--
Paige Miller
Amir
PROC Star

Hi @PaigeMiller,

 

Perhaps a comment or some brackets would have aided clarity, e.g.:

 

c[i] + (v[i] = 1);

I.e., perform the Boolean test v[i] = 1 which should return a 1 or a 0, then add that Boolean result to the retained value of c[i].

 

 

The output appears to match what the OP specified.

 

I hope that makes more sense.

 

Regards,

Amir.

PaigeMiller
Diamond | Level 26

Okay, this is the equivalent of

 

if v(i)=1 then c(i)+1;

in my code.

--
Paige Miller
ballardw
Super User

If you do not have millions of values this might be an approach:

Data have;
infile cards truncover ;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 2 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 3 0 0 0 0 0
8 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 6 0 0
0 0 0 0 0 0 7
0 0 0 1 0 1 0
0 0 1 1 1 0 0
;
run;
proc transpose data=have out=work.trans;
var var1-var7;
run;

data want;
  set work.trans;
  count1 =count(catx(',',of col:),'1');
  drop col: ;
run;

if you have more than 100 single digits you may need to estimate the length of the combined delimited string of all the rows of data and build a specific string variable long enough to hold that:

 

data want;
  set work.trans;
  length str $ 1000;
  str = catx(',',of col:);
  count1 =count(str,'1');
  drop col: str;
run;
jim_cai
Calcite | Level 5

with data set HAVE created, the procedure of frequency summary could be one of the following:

 

ods output onewayfreqs=freq;

proc freq data=have;
   table _all_/nopercent nocum list sparse;
quit;

ods output close;

data final;
   set;
   drop table frequency var1-var7 var_level f_:;
   var_level = strip(coalescec(of f_:));
   if var_level='1';
   var_name = scan(table, -1, '');
   count = frequency;
run;

or

 

data _null_;
   set have( obs=1);
   array var[7];
   n = dim(var);
   do i=1 to n;
      v_name = vname(var[i]);
      if i=1 then do;
         call execute('proc sql;');
         call execute('   create table final as');
      end; else call execute('   union');
      call execute('   select "'||trim(v_name)||'" as var_name, sum('||trim(v_name)||'=1) as count from have');
      if i=n then do;
         call execute('   ;');
         call execute('quit;');
      end;
   end;
run;

 

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
  • 11 replies
  • 3315 views
  • 0 likes
  • 6 in conversation