DATA Step, Macro, Functions and more

Concatenate multiple rows into a single string.

Accepted Solution Solved
Reply
Occasional Contributor YBM
Occasional Contributor
Posts: 5
Accepted Solution

Concatenate multiple rows into a single string.

Hi, I am frustrated after many tries and failing to concatinate multiple text variables into a long text string.

Lets assume that the text segments are PRIMEKEY in the data set PERSON_FILE (var1 var2 primekey var4 var5).

lets assume PRIMEKEY values are '123abc'  '456fdc'  '985liu'......

The desired result is a single string: '123abc','456fdc','985liu','...... :

I've tried 2 approaces which failed me:

approach 1:

 

DATA   RECKEYS (KEEP=SAMPLKEY);                                                                       
SET    PERSON_FILE END=EOD;                                       
   IF PRIMEKEY NOT EQ ' ' THEN DO;                                
       IF _N_ EQ 1 THEN SAMPLKEY="'"||PRIMEKEY||"'"; 
       ELSE SAMPLKEY=SAMPLKEY||",'"||PRIMEKEY||"'";                          
   END;                                            
   IF _N_ GT &SAMPLE_SIZE OR EOD THEN DO;                          
         OUTPUT; STOP;            
   END;                                                           
 RUN;                                                          

 

The second approach that I cant make work is:

PROC SORT  DATA=PERSON_FILE NODUPKEY; BY PRIMEKEY; RUN;

 

PROC SQL;                           
   SELECT DISTINCT "'"||SAMPLKEY||"'"
     INTO :RECKEY SEPARATED BY ','  
     FROM KEYS;                     
QUIT; RUN;                           
                                  
TITLE 'RECKEY STRING';              
PROC PRINT DATA=RECKEYS; RUN &DEBUG;


Accepted Solutions
Solution
‎01-12-2017 04:09 PM
Respected Advisor
Posts: 4,644

Re: Concatenate multiple rows into a single string.

Try something like:

 

DATA RECKEYS(KEEP=SAMPLKEY);
length samplkey $2000;
do until(eod or i=&sample_size); 
    SET PERSON_FILE END=EOD;
    if lengthn(primekey) > 0 then do;
       samplkey = catx(",", samplkey, catq("2AS", primekey));
       i + 1;
       end;
    end;
output; stop;
run;
PG

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 89

Re: Concatenate multiple rows into a single string.

I am unclear as to what your data look like and what you are attempting.

 

Do you have:

var1  var2 primekey var4 var5
1       2      123abc    4      5
2       3      456fdc     5      6
3       4      985liu      6      7

or something like

var1  var2 primekey1 primekey2 primekey3 var4 var5
1       2      123abc     234asd      345qwe     4      5
2       3      456fdc     345dfg        678yui       5      6
3       4      985liu      678bnm      345dfg       6      7

or just what?

 

What do you have now and what should it look like?

 

Occasional Contributor YBM
Occasional Contributor
Posts: 5

Re: Concatenate multiple rows into a single string.

Var1 var2 samplkey var4 ...

25 Jeff 123abc m

32 Hu 456fdc m

45 Waii 985liou f



The result strings the sample keys, each in quotes and separated by a comma.


Frequent Contributor
Frequent Contributor
Posts: 89

Re: Concatenate multiple rows into a single string.

Ah. I see. How many records are we talking and how long will this key be? And what on earth will you do with a giant text string?
Occasional Contributor YBM
Occasional Contributor
Posts: 5

Re: Concatenate multiple rows into a single string.

the first situation.
Super User
Posts: 5,081

Re: Concatenate multiple rows into a single string.

Both approaches are possible!  (Both might benefit from using the TRIM function in case your incoming string has trailing blanks that you want to get rid of.)

 

The first approach fails because the program assigns SAMPLKEY a length based on the first mention.  So its length is the length of PRIMEKEY + 2 (enough to hold two single quotes, but not enough to hold the entire string you would want to assemble).  So you could possible guess at a long length and issue this statement early in the DATA step:

 

length SAMPLKEY $32000;

 

The correct the code by adding TRIM:

 

if _n_=1 then samplkey = "'" || trim(primekey) || "'";

else samplkey = trim(samplkey) || ",'" || trim(primekey) || "'";

 

The second approach works as is (assuming that KEYS is the proper name for the incoming data set, not PERSON_FILE).  The problem is that you don't understand what it creates.  There is no output data set here that PROC PRINT can utilize.  Instead, there is a macro variable created.  You can see its contents by issuing this statement:

 

%put &reckey;

 

It too might benefit by adding the TRIM function.

Super User
Posts: 17,817

Re: Concatenate multiple rows into a single string.

What are you trying to do with this list? 

 

The second option has a few other possibilities, but note there's a limit to the length of a string, 64K characters or 32K depending on your system.

Use the quote function to add quotation marks is a bit cleaner and then it creates a macro variable that you can use in further programs if required.

 

proc sql noprint;
select quote(trim(name)) into :name_list  separated by ", "
from sashelp.class;
quit;

%PUT &name_list;

 

 

Occasional Contributor YBM
Occasional Contributor
Posts: 5

Re: Concatenate multiple rows into a single string.

I have not tried this solution. YBM.
Solution
‎01-12-2017 04:09 PM
Respected Advisor
Posts: 4,644

Re: Concatenate multiple rows into a single string.

Try something like:

 

DATA RECKEYS(KEEP=SAMPLKEY);
length samplkey $2000;
do until(eod or i=&sample_size); 
    SET PERSON_FILE END=EOD;
    if lengthn(primekey) > 0 then do;
       samplkey = catx(",", samplkey, catq("2AS", primekey));
       i + 1;
       end;
    end;
output; stop;
run;
PG
Occasional Contributor YBM
Occasional Contributor
Posts: 5

Re: Concatenate multiple rows into a single string.

Yours is the one I tried. Since it works, I have not tried the others' suggestions. Thanks.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 596 views
  • 0 likes
  • 5 in conversation