topic Re: Calculating numeric value from CSV table with alphanumeric values in SAS Enterprise Guide

Calculating numeric value from CSV table with alphanumeric values

sebster24 — Tue, 21 Oct 2014 08:53:29 GMT

Hello everyone,

I have currently a huge list of comma separated values in one column and i need to add +1 to the comma separated numeric values. The column contains alphanumeric characters.

I have tried and compiled this macro which does the following execution;

It looks at the comma separated alpha-numeric value in the cell under column "Pvalue", splits the values and then adds 1 to the numeric values, and then makes them a comma seperated list within the cell again.

So,

(pvalue) (results)

2009,2010,2011 ----> 2010, 2011,2012

2009,2010Full ----> 2010, 2011Full

CODE below;

/* DATA TABLE */

data table1;
infile datalines delimiter = '_';
input SrNo $1. Pvalue $20.;
datalines;
1_2009,2010,2011
2_2009,2010Full

;
run;

/* COMPILATION MACRO */

%macro adjustGenerationList(strg=);
%let newstr=%sysfunc(cat(&str,yy));
%let returnVal=;
%let stri=&strg.;
%put received: &newstr.;
%let cnt=1;
%let returnVal=&stri.;

%do %while(%qscan(%quote(&strg.),&cnt.,%STR(,)) ne %str());
%let gen = %sysfunc(strip(%qscan(%quote(&strg.),&cnt.,%STR(,))));
%put splitted gen: &gen;
%let gen1up=%eval(&gen+1);
%put gen after up1: &gen1up;

%if &cnt = 1 %then
%let returnVal=&gen1up;
%else %let returnVal=%sysfunc(cat(%quote(&returnVal),%str(,),&gen1up));
%put returnval: &returnVal.;
%let cnt=&cnt+1;
%end;

&returnVal.
%mend adjustGenerationList;

/* PROBLEM IN EXECUTION */

proc Sql;
select *,"%adjustGenerationList(str="||%str(pvalue)||")" as results
from table1
;
quit;

THE PROBLEM:

I am unable to run the code if the column name is specified i.e.(as above) Rather if I specify the year as below then it works, but places the value in different columns, rather than concatenated in a single column for each row.

/* SOMEWHAT FUNCTIONAL */

proc Sql;

create table work.trialtable as

select *,%adjustGenerationList(strg=%str(2009,2010,2011)) as xx

from t1

;

quit;

Please help me.

All help would be appreciated.

thank you.

Re: Calculating numeric value from CSV table with alphanumeric values

RW9 — Tue, 21 Oct 2014 09:11:11 GMT

Hi,

Well couple of questions. Firstly, why do you have the data in a SAS column separated by a comma, this doesn't make sense. CSV is a file transport format which is read into variables in a SAS dataset.

Secondly the data above does not look like a CSV file. The last row "2009,2010Full" is missing a column, which would be indicated by a comma after the word full, or prior to the word full.

So stage one, identify your data, does Full appear in col2 or in col3, if so then put the comma in. Also separate your data out into columns so you can work with it, if you need CSV output then deal with putting it out to a file later.

data have;
pvalue="2009,2010,2011"; output;
pvalue="2009,2010,Full"; output;
run;

data want (drop=i);
set have;
length new_pvalue $200.;
do i=1 to 3;
if anyalpha(scan(pvalue,i,',')) > 0 then new_pvalue=catx(',',strip(new_pvalue),strip(scan(pvalue,i,',')));
else new_pvalue=catx(',',strip(new_pvalue),strip(put(input(scan(pvalue,i,','),best.)+1,best.)));
end;
run;

Re: Calculating numeric value from CSV table with alphanumeric values

sebster24 — Tue, 21 Oct 2014 09:33:03 GMT

Hey,

Thank you for this. The data in the SAS column is an output that i received from an online source.

The 2009,2010,2011 can be of any length i.e.

data have;

pvalue="2007,2008,2009,2010,2011,2012"; output;

pvalue="2009,2010xyz"; output;

run;

The "Full" can be character/ word i.e. xyz, abc. The output table which you produced is perfect.

thanks once again

Re: Calculating numeric value from CSV table with alphanumeric values

RW9 — Tue, 21 Oct 2014 09:55:48 GMT

Something like:

data have;
pvalue="2007,2008,2009,2010,2011,2012"; output;
pvalue="2009,2010xyz"; output;
run;

data want;
set have;
length new_pvalue $200.;
i=1;
do until (scan(pvalue,i,',')="");
    if anyalpha(scan(pvalue,i,','))=0 then do;
      new_pvalue=catx(',',strip(new_pvalue),strip(put(input(scan(pvalue,i,','),best.)+1,best.)));
    end;
    else do;
      new_pvalue=catx(',',strip(new_pvalue),strip(put(input(substr(scan(pvalue,i,','),1,4),best.)+1,best.)),substr(scan(pvalue,i,','),5));
    end;
    i=i+1;
end;
run;

Note that if you don't want the comma before the text, then take the last bit out of the else do catx and just concatenate it.

Re: Calculating numeric value from CSV table with alphanumeric values

sebster24 — Tue, 21 Oct 2014 12:45:14 GMT

Genius!! Thank you. This worked like a charm.

Re: Calculating numeric value from CSV table with alphanumeric values

Ksharp — Tue, 21 Oct 2014 13:48:08 GMT

Did you also need to add an blank before 'Full' ?

data  table1;
infile datalines delimiter = '_';
input SrNo : $1. Pvalue $20.;
datalines;
1_2009,2010,2011
2_2009,2010Full
;
run;
data want;
 set table1;
new=prxchange('s/(,|\d(?=[a-zA-Z]))/$1 /o',-1,Pvalue);
run;

Xia Keshan