Desktop productivity for business analysts and programmers

Looping and Concat col in QB

Reply
Contributor
Posts: 37

Looping and Concat col in QB

Hi Gurus,

 

let say   If i have a Column 1 to Column 100 and want to group them by Col-Fruit, in order word i want to concat  Col1 to Col100 in Query builder, I would select

 

Col-Fruit under Select  Data and Add computed column with  " Catx(',', Col1,Col2,Col3........Col100)

 

are there any smart to loop or something that i don't have to type from Col1 to Col100 ?

 

Thank for your help in advance

Super User
Posts: 9,574

Re: Looping and Concat col in QB

There are limits to point-and-click interfaces.

Generating such repeating code in a macro is a breeze:

%macro concat(number);
data want;
set have;
length new_field $1000;
new_field = catx(','
%do i = 1 %to &number;
,col&i.
%end;
);
run;
%mend;
%concat(100)

Or you can create the code with call execute:

data _null_;
call execute('data want; set have; length new_field $1000; new_field = catx(","');
do i = 1 to 100;
  call execute(',col'!!strip(put(i,3.)));
end;
call execute('); run;');
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: Looping and Concat col in QB

Posted in reply to KurtBremser

Thx, KurtBremser.

 

Yeah ,same as last time, would like to know how to do it in Sas EG Man Very Happy

Super User
Posts: 9,574

Re: Looping and Concat col in QB


SASNE wrote:

Thx, KurtBremser.

 

Yeah ,same as last time, would like to know how to do it in Sas EG Man Very Happy


Simple. Open a code node and write the code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,615

Re: Looping and Concat col in QB

Catx(',', of Col1-Col100)

Super User
Posts: 22,850

Re: Looping and Concat col in QB

Unfortunately there are no shortcuts within SAS EG or PROC SQL. Data steps allow for variable shortcut lists as illustrated by KSharp. 

Contributor
Posts: 37

Re: Looping and Concat col in QB

i See. thx you for your help  Man Very Happy

Valued Guide
Posts: 505

Re: Looping and Concat col in QB

Looping and Concat col in QB

This problem does not lend itself to mouse surfing?

inspired by
https://goo.gl/C5qmVX
https://communities.sas.com/t5/SAS-Enterprise-Guide/Looping-and-Concat-col-in-QB/m-p/335958


This solution is a bit advanced and probably
should not be used by inexperienced programmers.

You do not need to know how many columns are in the data.


HAVE
====

Up to 40 obs WORK.HAVE total obs=5

 O COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 FRUIT

 1 A086 A090 A098 A002 A083 A042 A031 A090 A091 A088 APPLE
 2 A055 A008 A014 A031 A074 A066 A084 A000 A024 A062 APPLE
 3 A071 A004 A010 A038 A006 A044 A054 A033 A039 A085 APPLE
 4 A057 A047 A098 A010 A024 A002 A041 A073 A025 A046 APPLE
 5 A081 A098 A052 A099 A036 A081 A069 A048 A024 A040 APPLE

WANT
====

COLALL                                                        FRUIT
-------------------------------------------------------------------
A086,A090,A098,A002,A083,A042,A031,A090,A091,A088             APPLE
A055,A008,A014,A031,A074,A066,A084,A000,A024,A062             APPLE
A071,A004,A010,A038,A006,A044,A054,A033,A039,A085             APPLE
A057,A047,A098,A010,A024,A002,A041,A073,A025,A046             APPLE
A081,A098,A052,A099,A036,A081,A069,A048,A024,A040             APPLE

WORKING CODE

   rc=dosubl('
   proc sql;
     select
       catx(",",&colcat.) as colall length=44
      ,fruit
     from
       have
     ;quit;
');


FULL SOLUTION
=============

* create some data;
data have;
 array cols[10] $8 col1-col10;
 do rep=1 to 5;
    do var= 1 to 10;
       cols[var]='A'!!put(int(100*uniform(-1)),z3.);
    end;
    fruit='APPLE';
    keep c: fruit;
    output;
 end;
run;quit;

* select concatenated cols and fruit;
data null_;
 length getcol $1024;
 set have(obs=1);
 array cols[*] c:;
 do i=1 to dim(cols);
  getcol=catx(',',getcol,vname(cols[i]));
 end;
 call symputx('colcat',strip(getcol));
 put getcol=;
 rc=dosubl('
   proc sql;
     select
       catx(",",&colcat.) as colall length=60
      ,fruit
     from
       have
   ;quit;
');
 stop;
run;quit;

Ask a Question
Discussion stats
  • 7 replies
  • 249 views
  • 0 likes
  • 5 in conversation