BookmarkSubscribeRSS Feed
sebster24
Quartz | Level 8

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.

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sebster24
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sebster24
Quartz | Level 8

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

Ksharp
Super User

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1140 views
  • 3 likes
  • 3 in conversation