06-12-2013 05:29 AM
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
In the next step I then run a query on the above result in which I perform a cats function which as result returns:
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,
06-12-2013 06:41 AM
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.
select name into: columns separated by ','
where libname = 'YOURLIB' and
name not in('PRODUCT','OTHER_COLUMN');
Then use it with catx:
06-12-2013 09:59 AM
Easier with DATA step than with SQL as you can use variable lists.
cat_cust = catx('_', of column: );