BookmarkSubscribeRSS Feed
ZRick
Obsidian | Level 7

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;

26 REPLIES 26
Linlin
Lapis Lazuli | Level 10

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
Obsidian | Level 7

Can you help explain little bit:

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

Appreciate it!

Haikuo
Onyx | Level 15

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
Opal | Level 21

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
Onyx | Level 15

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

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Opal | Level 21

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
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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.

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
  • 26 replies
  • 2027 views
  • 1 like
  • 6 in conversation