Cat function on a transpose which might variable number of columns

Chr1st1an — Wed, 12 Jun 2013 09:29:49 GMT

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

Re: Cat function on a transpose which might variable number of columns

LinusH — Wed, 12 Jun 2013 10:41:49 GMT

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

Re: Cat function on a transpose which might variable number of columns

Tom — Wed, 12 Jun 2013 13:59:12 GMT

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

data want;

set have;

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

run;

topic Cat function on a transpose which might variable number of columns in SAS Enterprise Guide

Cat function on a transpose which might variable number of columns

Re: Cat function on a transpose which might variable number of columns

Re: Cat function on a transpose which might variable number of columns