topic Re: Looping and Concat col in QB in SAS Enterprise Guide

Looping and Concat col in QB

SASNE — Sun, 26 Feb 2017 07:25:14 GMT

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

Re: Looping and Concat col in QB

Kurt_Bremser — Sun, 26 Feb 2017 08:12:16 GMT

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;

Re: Looping and Concat col in QB

SASNE — Sun, 26 Feb 2017 08:30:32 GMT

Thx, KurtBremser.

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

Re: Looping and Concat col in QB

Kurt_Bremser — Sun, 26 Feb 2017 08:37:35 GMT

@SASNE wrote:

Thx, KurtBremser.

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

Simple. Open a code node and write the code.

Re: Looping and Concat col in QB

Ksharp — Sun, 26 Feb 2017 10:26:43 GMT

Catx(',', of Col1-Col100)

Re: Looping and Concat col in QB

Reeza — Sun, 26 Feb 2017 13:33:29 GMT

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

Re: Looping and Concat col in QB

SASNE — Sun, 26 Feb 2017 14:30:27 GMT

i See. thx you for your help

Re: Looping and Concat col in QB

rogerjdeangelis — Mon, 27 Feb 2017 02:47:29 GMT

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;