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;
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;
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?
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.
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;
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;
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.
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.