BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

 

suppose I have the following data:

var1 var2 var3 var4 var5 var6
a a a b b b
a b a b a b
b b b a a a

 

what I would like to obtain is a new data set consisting of the above data set where the value "a" occurs at least 2 times over the variables var1 to var 4.

So here I will end up with 1st row because "a" occurs 3 times within var1 - var4,

and the second row because it occurs 2 times.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Here is one way to do it:

 

data have;
  input (var1-var6) ($);
  cards;
a a a b b b
a b a b a b
b b b a a a
;

data want;
  set have;
  if countc(catt(of var1-var4),'a') ge 2 then output;
run;

HTH,

Art

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

Here is one way to do it:

 

data have;
  input (var1-var6) ($);
  cards;
a a a b b b
a b a b a b
b b b a a a
;

data want;
  set have;
  if countc(catt(of var1-var4),'a') ge 2 then output;
run;

HTH,

Art

 

Astounding
PROC Star

In real life, is the key string only 1 character long?  If so, Art's solution works just fine.  If not, you may need to rethink your approach.  Another possibility:

 

data want;

set have;

array vars {6} var1-var6;

totcount = 0;

do _n_=1 to 4;

   if vars{_n_}='a' then totcount + 1;

end;

drop totcount;

if totcount >= 2;

run;

 

Other details might matter.  Does capitalization play a role?  Do partial words count?  For example, if you are searching for "cancer" would "cancerous" count?  The more details you can supply, the better the answer you will receive.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

A slight change to @art297's great example should cover words also:

data have;
  input (var1-var6) ($);
  cards;
a a a b b b
a b a b a b
b b b a a a
;
run;

data want;
  set have;
  sum_of_a=6 - countw(tranwrd(catx(',',of var:),"a",""),",","blank");
run;

What I do is drop any "a" results, then count what is left and take that off total.  If you know the other options then you could go the other way of course.

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
  • 3 replies
  • 776 views
  • 2 likes
  • 4 in conversation