problem catx with macro variable passed in

[ZRick]

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;

[Linlin]

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;

[ZRick]

Can you help explain little bit:

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

Appreciate it!

[Haikuo]

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

[art297]

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 col:);

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.

[Haikuo]

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

[art297]

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

[Haikuo]

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

[Tom]

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.

[ZRick]

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.

[ZRick]

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;

[art297]

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 col:);

run;

[Tom]

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.

[ZRick]

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?

[Tom]

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.