DATA Step, Macro, Functions and more

problem catx with macro variable passed in

Reply
Frequent Contributor
Posts: 133

problem catx with macro variable passed in

Main issue is that the macro variable mv is not passed, so, catx can't move on, how do I fix it?

data tmp;
length stmt $50;
  input cat stmt &$;
  datalines4;
1 I am
1 good writer;
2 statement sample here
;;;;


proc transpose data=tmp out=trans (drop=_name_);
by cat;
var stmt;
run;

/*sets number of cat variables into mv variable*/
proc sql;
   select count(cat) into :mv from tmp group by cat;
   %let mv = &mv;
quit;
data fnl(keep=x);
set trans;
x=catx(" ",col1-col&mv);
run;

Super Contributor
Posts: 1,636

problem catx with macro variable passed in

how about:

data tmp;

length stmt $50;

  input cat stmt &$;

  datalines4;

1 I am

1 good writer;

2 statement sample here

;;;;

proc transpose data=tmp out=trans (drop=_name_);

by cat;

var stmt;

run;

/*sets number of cat variables into mv variable*/

proc sql;

   select count(cat) into :mv from tmp group by cat;

quit;

%let mv = &mv;

data fnl(keep=x);

set trans;

x=catx(" ",of col1-col&mv);

run;

Frequent Contributor
Posts: 133

problem catx with macro variable passed in

Can you help explain little bit:

why should I take %let mv=&mv; out side of proc sql?

Appreciate it!

Respected Advisor
Posts: 3,156

Re: problem catx with macro variable passed in

macro compiles way ahead of regular SAS code. so if you leave &mv inside SQL, it will try to be resovled long before 'select into' taking effect.

Haikuo

PROC Star
Posts: 7,468

problem catx with macro variable passed in

What are you trying to accomplish?  From your code you appear to be trying to reach:

data tmp;

length stmt $50;

  input cat stmt &$;

  datalines4;

1 I am

1 good writer;

2 statement sample here

;;;;

proc transpose data=tmp out=trans (drop=_name_);

  by cat;

  var stmt;

run;

data fnl(keep=x);

  set trans;

  x=catx(" ",of colSmiley Happy;

run;

However, to answer your question, you are already creating a macro variable with your proc sql code.  Why try recreate the same variable with a %let statement.  Possibly I have missed something.

Respected Advisor
Posts: 3,156

problem catx with macro variable passed in

Art, I believe OP just wanted to get rid of blanks.

PROC Star
Posts: 7,468

problem catx with macro variable passed in

Haikuo,  possibly, but that was never mentioned in the thread.

Respected Advisor
Posts: 3,156

problem catx with macro variable passed in

I know. Can you count how many time you have played mind-reading Smiley Wink?

Super User
Super User
Posts: 7,039

Re: problem catx with macro variable passed in

If you are trying to determine how many columns PROC TRANSPOSE will create you are counting the wrong thing.

proc sql noprint;

  select max(n) into :mv

  from (select count(*) as n from tmp group by cat)

;

quit;

To eliminate the spaces from your macro variable you can also use the SEPARATED BY clause after the target macro variable name.

That said it is a lot easier and clearer to specify the variable range using COL: as Art suggested than to bother to re-process the data to find the count.

Frequent Contributor
Posts: 133

problem catx with macro variable passed in

I want to get final 3 lines:

I am good writer;

Statement sample here

Hello my friend where are u

I use mv to pass it in the number of COL and then use catx to concatenate them

But it does not give me the right answer, I don't know how to fix it.

Frequent Contributor
Posts: 133

problem catx with macro variable passed in

I don't think this is working, I added another group, catx, I lost "friend where are u"

data tmp;
length stmt $50;
  input cat stmt &$;
  datalines4;
1 I am
1 good writer;
2 statement sample here
3 Hello
3 my
3 friend where are u
;;;;
proc transpose data=tmp out=trans (drop=_name_);
by cat;
var stmt;
run;

/*sets number of cat variables into mv variable*/
proc sql;
   select count(cat) into :mv from tmp group by cat;
quit;

%let mv = &mv;

data fnl(keep=x);
set trans;
x=catx(" ",of col1-col&mv);
run;

PROC Star
Posts: 7,468

Re: problem catx with macro variable passed in

I don't lose anything with the following:

data tmp;

length stmt $50;

  input cat stmt &$;

  datalines4;

1 I am

1 good writer;

2 statement sample here

3 Hello

3 my

3 friend where are u

;;;;

proc transpose data=tmp out=trans (drop=_name_);

  by cat;

  var stmt;

run;

data fnl(keep=x);

  set trans;

  x=catx(" ",of colSmiley Happy;

run;

Super User
Super User
Posts: 7,039

Re: problem catx with macro variable passed in

You are still not counting the right thing.  You are counting the number of rows that PROC TRANSPOSE will generate.  You need to count the number of columns.

Frequent Contributor
Posts: 133

problem catx with macro variable passed in

I count the number of row by group. so 1st group has 2 rows, 2nd has 1, 3rd has 3,

Then I transpose by combining the row, 1st group is 2 rows needed to be grouped, 2nd 1 row, 3rd needed 3 rows to be grouped.

I think count logic is right, unless I miss something?

Super User
Super User
Posts: 7,039

Re: problem catx with macro variable passed in

Your select statement is counting the number of rows per value of CAT and then only outputting the first count into the macro variable.

This is how the INTO clause works in PROC SQL when you select more than one row into a single macro variable.  If you add the SEPARATED BY or specify a range of target macro variables you will be able to see the other counts.

The logic I posted adds another summation step to take the maximum number of rows over all of the values of CAT.  This is what will determine how many columns are created by PROC TRANSPOSE.

Ask a Question
Discussion stats
  • 26 replies
  • 920 views
  • 0 likes
  • 6 in conversation