BookmarkSubscribeRSS Feed
SASNE
Obsidian | Level 7

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

7 REPLIES 7
Kurt_Bremser
Super User

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;
SASNE
Obsidian | Level 7

Thx, KurtBremser.

 

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

Ksharp
Super User

Catx(',', of Col1-Col100)

Reeza
Super User

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

SASNE
Obsidian | Level 7

i See. thx you for your help  Man Very Happy

rogerjdeangelis
Barite | Level 11
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1197 views
  • 0 likes
  • 5 in conversation