problem catx with macro variable passed in

[Linlin]

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(distinct cat) into :mv from tmp ;

quit;

%let mv = &mv;

data fnl(keep=x);

length x $ 100;

set trans;

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

run;

proc print;run;

                        obs        x

                         1     I am good writer;

                         2     statement sample here

                         3     Hello my friend where are u

[art297]

Linlin,  Your post, like the earlier onesl, confuses me.  Why use proc sql to create a macro variable, then use a %let statement to create the same macro variable that you created, and then use it when you could have just used col: ??

In fact, why create the macro variable at all?

Have I missed something in this thread?

[Linlin]

Hi Art,

The macro variable is not necessary at all. I just help Rick to fix his original code as he requested.

Thanks!

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;

[FriedEgg]

I agree with Art, when doing the transpose there is no reason to create the macro variable with you can use the variable list of col: but why do the transpose at all?

data foo;
 input cat stmt & $20.;
 cards;
1 I am
1 a good writer
2 statement sample here
3 Hello
3 my
3 friend where are you
;
run;
data bar;
 set foo;
 by cat;
 length slurp $512;
 retain slurp;
 if first.cat then slurp=stmt; 
  else slurp=catx(' ',slurp,stmt);
 if last.cat then output;
 keep slurp;
run;
slurp
I am a good writer           
statement sample her         
Hello my friend where are you

Additionally there are two other methods to get the number of columns from after the transpose that I do not think were mentioned yet like:

26         proc transpose
27          data=foo
28          out=bar(drop=_:);
29          by cat;
30          var stmt;
31         run;
NOTE: There were 6 observations read from the data set WORK.FOO.
NOTE: The data set WORK.BAR has 3 observations and 5 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
32         
33         proc sql noprint;
34          select nvar-1 into :nvar from sashelp.vtable where libname='WORK' and memname='BAR';
35          select count(*) into :nvar2 from sashelp.vcolumn where libname='WORK' and memname='BAR' and name like 'COL%';
36          /* doesn't matter if I resolve here either */
37          %put nvar=%trim(%left(&nvar)) and nvar2=%trim(%left(&nvar2));
nvar=3 and nvar2=3
38         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

[ZRick]

FriedEgg: I tried your other two methods, looks like they don't work.

[FriedEgg]

You will need to post more accurate samples of what you are dealing with then.  Are you saying you copy/pasted my code and it doesn't work for you?

[ZRick]

Here are additional method you suggested:

data foo;
input cat stmt & $20.;
cards;
1 I am
1 a good writer
2 statement sample here
3 Hello
3 my
3 friend where are you
;
run;

/*method3:*/
proc transpose data=foo out=bar(drop=_:);
by cat;
var stmt;
run;

Another method:

proc sql ;

select nvar-1 into :nvar from sashelp.vtable where libname='WORK' and memname='BAR';

select count(*) into :nvar2 from sashelp.vcolumn where libname='WORK' and memname='BAR' and name like 'COL%';

/* doesn't matter if I resolve here either */

%put nvar=%trim(%left(&nvar)) and nvar2=%trim(%left(&nvar2));

nvar=3 and nvar2=3;

quit;

[FriedEgg]

I did not put the last step, which using that method would be to actually concatentate the fields using the nvar I calculated differently then others had posted.  If you see my comment:

FriedEgg wrote:
Additionally there are two other methods to get the number of columns from after the transpose that I do not think were mentioned yet like: 

I say the code is only to calculate the number of columns to concatenate later...

You need to do something like the following to complete your task:

data bar(keep=x);
length x $100;
set bar;
x=catx(" ",of col1-col&nvar);
run;

Although my first suggestion saves a lot of processing and I/O if you care actually dealing with larger data.

[ZRick]

Hello FriedEgg and art297,

Can you tell me the usage of ":" in x=catx(" ",of col:);?

it looks like it save the portion of using proc sql to create a macro variable to determine the number of rows too be concatenated in the catx.

[Haikuo]

":" here is wildcards. 'col:' represents all of the variable names starting with 'col'.

[FriedEgg]

The ":" creates a variable list for all variables in the dataset with a name starting with col followed by anything.

[Tom]

The documentation calls this a "name prefix list".

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695105.htm