BookmarkSubscribeRSS Feed
rajeshalwayswel
Pyrite | Level 9
 data have;
  input a $;
  cards;
  car
  bus
  jeep
  aeroplane
  maruti
run;

want:

 a    count
car   1
bus    1
other  3
7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26
proc sql;
  create table want as
  select  a,
          count(*) as count
  from    (select case when a not in ("car","bus") then "other" else a end as a from have)
  group by a;
quit;

But this is the same as simply categorising your data beforehand into the required output:

data have;
  length a $20;
  input a $;
  if a not in ("car","bus") then a="other";
cards;
car
bus
jeep
aeroplane
maruti
;
run;
rajeshalwayswel
Pyrite | Level 9

I have more then 10 records is any other way to do. Thanks for the response.

andreas_lds
Jade | Level 19

@rajeshalwayswel wrote:

I have more then 10 records is any other way to do. Thanks for the response.


Why is the number of records a problem?

rajeshalwayswel
Pyrite | Level 9
if a not in ("car","bus") then a="other";

if I use this step so I need to mention all the rest of them in the operators.

andreas_lds
Jade | Level 19

@rajeshalwayswel wrote:
if a not in ("car","bus") then a="other";

if I use this step so I need to mention all the rest of them in the operators.


So, after having a second look at the data you posted, you need to count the observations beginning with the third one, right?

Try:

data want;
   set have end=jobDone;

   retain count;

   if _n_ < 3 then do;
      count = 1;
      output;
      count = 0;
   end;
   else do;
      count = count + 1;
   end;

   if jobDone then do;
      a = 'other';
      output;
   end;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post test data which describes your problem, I can only work with what you provide.

Ksharp
Super User
data have;
  input a $;
  cards;
  car
  bus
  jeep
  aeroplane
  maruti
run;

proc format ;
value $fmt
 'car'='car'
 'bus'='bus'
 other='other';
run;

proc freq data=have;
table a /out=want;
format a $fmt.;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 821 views
  • 4 likes
  • 4 in conversation