Cat function on a transpose which might variable number of columns

[Chr1st1an]

Hi all,

I am a beginner to intermediate SAS EG user and mainly work with queries since I have little programming knowledge.

I am using SAS EG 4.3 and I am facing the following problem, in my project at one of the last steps I am performing a transpose on a data set which returns sth. like this:

Product      Column1          Column2          Column3          Column4          Column5

a               CustomerZ        CustomerY     CustomerX        CustomerW     CustomerV

b                                       CustomerZ     CustomerW

c               CustomerZ        CustomerY     CustomerX        CustomerW    

d               CustomerV

In the next step I then run a query on the above result in which I perform a cats function which as result returns:

Product     Cat_Cust

a               CustomerZ_CustomerY_CustomerX_CustomW_CustomerV

b               CustomerZ_CustomerW

c               CustomerZ_CustomerY_CustomerX_CustomerW

d               CustomerV

In order to build this cats result the function looks sth. like cats(t1.column1,'_',column,'_',etc.....)

My question now is depending on which selections I make in my time period or various other prompts in the project the total number of columns on which I perform a transpose might change which then can lead to the project failing.

Is there a way how I can tell SAS in the cats function to use whaterver number of columns are available in the previous data set and seperate them with '_' ?

Thanks a lot for any help on this,

Christian

[LinusH]

Probably you need a pre step that will figure out which columns you have.

One way to do it is to query dictionary.columns and store the result in a macro variable.

proc sql;

select name into: columns separated by ','

from dictionary.columns

where libname = 'YOURLIB' and

name not in('PRODUCT','OTHER_COLUMN');

Then use it with catx:

catx('_',&COLUMNS.)

[Tom]

Easier with DATA step than with SQL as you can use variable lists.

data want;

set have;

cat_cust = catx('_', of column: );

run;