BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
YBM
Calcite | Level 5 YBM
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

9 REPLIES 9
HB
Barite | Level 11 HB
Barite | Level 11

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?

 

YBM
Calcite | Level 5 YBM
Calcite | Level 5
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.


HB
Barite | Level 11 HB
Barite | Level 11
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?
YBM
Calcite | Level 5 YBM
Calcite | Level 5
the first situation.
Astounding
PROC Star

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.

Reeza
Super User

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;

 

 

YBM
Calcite | Level 5 YBM
Calcite | Level 5
I have not tried this solution. YBM.
PGStats
Opal | Level 21

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
YBM
Calcite | Level 5 YBM
Calcite | Level 5
Yours is the one I tried. Since it works, I have not tried the others' suggestions. Thanks.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 9537 views
  • 1 like
  • 5 in conversation