Desktop productivity for business analysts and programmers

cat or catx unknow number of columns

Reply
Contributor
Posts: 42

cat or catx unknow number of columns

I just proc transposed my data and its giving me Column1 to ColumnX and the x number will keep changing how do i do a Catx( ) calculation when i dont know the number of columns 

Super User
Posts: 2,068

Re: cat or catx unknow number of columns

if i understand your needs correctly, you could use variable lists with a common name prefix followed by a : (colon)

 

Example:

proc transpose data=sashelp.class out=want;
var name;
run;

data w;
set want;
k=catx('__',of col:);
run;
Contributor
Posts: 42

Re: cat or catx unknow number of columns

Posted in reply to novinosrin
I have tried that but it doesn’t seem to work. The transpose give data as column1,column2...
And I have tried x=catx(' ‘,of columnSmiley Happy; but it does not work.
Super User
Posts: 2,068

Re: cat or catx unknow number of columns

Please show us the community a sample of your data, your code , log and your expected output so that somebody will be able to help you

Respected Advisor
Posts: 4,797

Re: cat or catx unknow number of columns

@hk2013

Keyword PREFIX in Proc Transpose allows you to define how transposed variables get called.

 

Code like below should work - else please post representative sample data, and your code (please test that the code works for the sample data) and show us where things aren't working for you.

proc transpose data=sashelp.class out=transposed prefix=NEWCOL_;
  var name;
run;

/* determine required length for variable holding the concatenated string */
proc sql noprint;
  select put(sum(length)+count(*),best32. -l) into :string_length
  from dictionary.columns
  where 
    libname='WORK' 
    and memname='TRANSPOSED' 
    and upcase(name) like 'NEWCOL^_%' escape '^'
  ;
quit;

/* create ds with variable holding the concatenated string */
data want;
  set transposed;
  length k $&string_length.;
  k=catx('|',of NEWCOL_:);
  drop NEWCOL_:;
run;
Super User
Posts: 24,026

Re: cat or catx unknow number of columns

Posted in reply to novinosrin

Proc Transpose allows you to specify a custom prefix, for example PRE. 

Use that and make sure it doesn’t interfere with your other variable name. 

 

And then use the OF and colon as indicated. 

 

New = catt(of PRE:);

New2 = catx('-', of PRE:);
Super User
Posts: 6,935

Re: cat or catx unknow number of columns

As long as you need a DATA step to combine all the transposed values, why not just skip PROC TRANSPOSE?  You can certainly use a DATA step on the original data, to get the same effect.  The variable names are unknown, so here's a guess as to what the program might look like:

 

data want;

set have;

by state;

length all_values $ 2000;

if first.state then all_values = result;

else all_values = catx('|', all_values, result);

if last.state;

run;  

Super User
Posts: 13,942

Re: cat or catx unknow number of columns


@hk2013 wrote:

I just proc transposed my data and its giving me Column1 to ColumnX and the x number will keep changing how do i do a Catx( ) calculation when i dont know the number of columns 


If you do not know how many variables you will be CATing together how do you know how long to make the variable that is the target?

 

From the documentation:

In a DATA step, if the CATX function returns a value to a variable that has not previously been assigned a length, then that variable is given a length of 200 bytes

 

The other CAT functions are similar.

So if you have 25 variables of length 9 + inserting comma you need 25*9+24=249 characters minimum length to store the result. So it may be that your result will be truncated because you do not have enough length for the concatenated string. I suggest finding a step to determine how long you need that CATX result to be or else you'll be back saying  "it didn't work".

 

 

 

Respected Advisor
Posts: 4,797

Re: cat or catx unknow number of columns


@hk2013 wrote:

I just proc transposed my data and its giving me Column1 to ColumnX and the x number will keep changing how do i do a Catx( ) calculation when i dont know the number of columns 


@hk2013 That's why I've added this proc SQL step in my previous post which calculates the required length.

Ask a Question
Discussion stats
  • 8 replies
  • 301 views
  • 0 likes
  • 6 in conversation