BookmarkSubscribeRSS Feed
hk2013
Fluorite | Level 6

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 

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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;
hk2013
Fluorite | Level 6
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.
novinosrin
Tourmaline | Level 20

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

Patrick
Opal | Level 21

@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;
Reeza
Super User

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:);
Astounding
PROC Star

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;  

ballardw
Super User

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

 

 

 

Patrick
Opal | Level 21

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 6148 views
  • 0 likes
  • 6 in conversation