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

DATA COMMENT;

INPUT ID COMMENT $ 150.;

INFILE DATALINES truncover;

DATALINES;

1 SERVICE IS GOOD but can improve

2 SERVICE IS BAD

3 STAFF CAN IMPROVE and also the food

4 happy customer

;

 

 

options mprint mlogic symbolgen;

%macro word_count(input=,var=,output=,order=);

data temp;

set &input.;

%do i = 1 %to %sysfunc(countw(&var.));

var1 = scan(&var.,&i.);

output;

%end;

run;

 

proc sql;

create table &output. as select var1, count(*) as count from temp group by var1 order by count &order.;

quit;

 

%mend word_count;

 

%word_count(input=comment,output=new,var=comment,order=desc);

 

LOg Message :

MLOGIC(WORD_COUNT): %DO loop beginning; index variable I; start value is 1; stop value is 1; by

value is 1.

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

You problem seems to be with the COUNTW function, when &var=COMMENT, then %sysfunc(countw(&var)) is equal to countw('COMMENT'), which is one.

I think you should drop the macro loop and use a data step loop instead:

%macro word_count(input=,var=,output=,order=);

data temp;

  set &input.;

  do _N_ = 1 to countw(&var.);

    var1 = scan(&var.,_N_);

    output;

    end;

run;

 

proc sql;

  create table &output. as select var1, count(*) as count from temp group by var1 order by count &order.;

quit;

 

%mend word_count;

 

I used _N_ as a temporary variable, as you save a DROP statement that way.

View solution in original post

4 REPLIES 4
s_lassen
Meteorite | Level 14

You problem seems to be with the COUNTW function, when &var=COMMENT, then %sysfunc(countw(&var)) is equal to countw('COMMENT'), which is one.

I think you should drop the macro loop and use a data step loop instead:

%macro word_count(input=,var=,output=,order=);

data temp;

  set &input.;

  do _N_ = 1 to countw(&var.);

    var1 = scan(&var.,_N_);

    output;

    end;

run;

 

proc sql;

  create table &output. as select var1, count(*) as count from temp group by var1 order by count &order.;

quit;

 

%mend word_count;

 

I used _N_ as a temporary variable, as you save a DROP statement that way.

ruchi11dec
Obsidian | Level 7

Hey thanks for the reply..
I was not able to understand..initially I wrote the simple program and then I decided to do it in the macro way, and I was clueless why it is not working... Yes it works this way..Thanks

RW9
Diamond | Level 26 RW9
Diamond | Level 26

What exactly is it you are trying to do with this code?  The problem I see it is that you have fallen for the age old issue of doing macro - which is nothing more than a text find/replace, rather than get a good basis in the actual programming language which is Base SAS.  None of the code you presented actually seems to do anything meaningful, the macro and the macro loop for instance just generate a whole lot of Base SAS statements, which you can of course achieve in many ways, but simply enough by:

data have;
  input id comment $ 150.;
  infile datalines truncover;
datalines;
1 SERVICE IS GOOD but can improve
2 SERVICE IS BAD
3 STAFF CAN IMPROVE and also the food
4 happy customer
;
run;

data want;
set have;
count=countw(comment);
run;

Notice that I don't code in shouting case, and avoid using things like "new" for dataset names, keywords can cause confusion.

 

ruchi11dec
Obsidian | Level 7

Hey..yes I know this does not makes sense.. I just tried to simplify the whole big program I was working on... 🙂

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