topic Re: problem catx with macro variable passed in in SAS Programming

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 00:24:45 GMT

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;

problem catx with macro variable passed in

Linlin — Fri, 09 Mar 2012 00:41:55 GMT

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;

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 01:26:56 GMT

Can you help explain little bit:

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

Appreciate it!

problem catx with macro variable passed in

art297 — Fri, 09 Mar 2012 01:40:45 GMT

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.

Re: problem catx with macro variable passed in

Haikuo — Fri, 09 Mar 2012 01:41:06 GMT

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

problem catx with macro variable passed in

Haikuo — Fri, 09 Mar 2012 01:42:03 GMT

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

problem catx with macro variable passed in

art297 — Fri, 09 Mar 2012 01:44:27 GMT

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

problem catx with macro variable passed in

Haikuo — Fri, 09 Mar 2012 01:50:11 GMT

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

Re: problem catx with macro variable passed in

Tom — Fri, 09 Mar 2012 01:58:59 GMT

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.

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 02:02:55 GMT

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;

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 02:06:32 GMT

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.

Re: problem catx with macro variable passed in

art297 — Fri, 09 Mar 2012 02:13:12 GMT

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;

Re: problem catx with macro variable passed in

Tom — Fri, 09 Mar 2012 02:13:29 GMT

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.

Re: problem catx with macro variable passed in

Linlin — Fri, 09 Mar 2012 03:03:53 GMT

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

Re: problem catx with macro variable passed in

art297 — Fri, 09 Mar 2012 03:35:51 GMT

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?

Re: problem catx with macro variable passed in

Linlin — Fri, 09 Mar 2012 03:55:13 GMT

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;

Re: problem catx with macro variable passed in

FriedEgg — Fri, 09 Mar 2012 04:37:51 GMT

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

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 19:12:31 GMT

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?

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 19:19:51 GMT

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.

problem catx with macro variable passed in

ZRick — Fri, 09 Mar 2012 19:48:21 GMT

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