topic Re: cat or catx unknow number of columns in SAS Enterprise Guide

cat or catx unknow number of columns

hk2013 — Sat, 12 May 2018 00:03:48 GMT

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

Re: cat or catx unknow number of columns

novinosrin — Sat, 12 May 2018 00:14:03 GMT

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;

Re: cat or catx unknow number of columns

hk2013 — Sat, 12 May 2018 00:37:33 GMT

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 column:); but it does not work.

Re: cat or catx unknow number of columns

novinosrin — Sat, 12 May 2018 00:38:59 GMT

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

Re: cat or catx unknow number of columns

Reeza — Sat, 12 May 2018 02:31:28 GMT

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

Re: cat or catx unknow number of columns

Patrick — Sat, 12 May 2018 02:37:58 GMT

@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;

Re: cat or catx unknow number of columns

Astounding — Sat, 12 May 2018 12:39:52 GMT

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;

Re: cat or catx unknow number of columns

ballardw — Mon, 14 May 2018 15:15:06 GMT

@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".

Re: cat or catx unknow number of columns

Patrick — Mon, 14 May 2018 23:46:20 GMT

@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.