topic extract delimeter names only using proc sql into clause in SAS Programming

extract delimeter names only using proc sql into clause

pavank — Mon, 01 Jul 2024 07:25:31 GMT

Hi Experts,

Here I am trying to extract only delimiters names from the below dataset using proc sql into clause

suppose we have multiple delimiters are there in huge data how to achieve dynamically

data cricketers;
input name$70.;
cards;
Curtyly Ambrose
Neil,Fairbrother
Kenny#Benjamin
sachin tendulkar
sunil gavaskar
Rohan,Gavaskar
Dhoni
virat
;
run;

proc sql noprint;
    select name into :delimeter_names separated by ' '
    from cricketers
    where index(name, ',') > 0 or index(name, '#') > 0;
quit;

%put &delimeter_names;

Re: extract delimeter names only using proc sql into clause

Ksharp — Mon, 01 Jul 2024 07:40:02 GMT

You could try FINDC() .

data cricketers;
input name$70.;
cards;
Curtyly Ambrose
Neil,Fairbrother
Kenny#Benjamin
sanchi tendulkar
sunil gavaskar
Rohan,Gavaskar
Dhoni
virat
;
run;

proc sql noprint;
    select name into :delimeter_names separated by ' '
    from cricketers
    where findc(name,',','p') > 0;
quit;

%put &delimeter_names;

Re: extract delimeter names only using proc sql into clause

Kurt_Bremser — Mon, 01 Jul 2024 07:53:20 GMT

What will you be doing with this list of data later on?

Putting a mass of data (particularly when it contains delimiting characters which can wreck a macro call) into a macro variable is, in the large majority of cases, a BAD IDEA.

Data is best kept in datasets.

PS you yourself talk about "huge data", so this can very easily crack the 64k limit of macro variables. Forget the macro variable approach.