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

Hi Everyone,

i have a looping and substitution problem in sas 

 

The Data is 

 

data have;
infile datalines;
input string :$150.;
datalines;
stevdend*kdioror*kdkdkd
SVD*H2459**HC:93000**1~
fakdlkfjajdf*kdkfkldlk*kdkdk
dkfjaokldjfkj*jkdkjfk*kdkkd
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1090F:8P**0~
qwwkfjajdf*grekfkldlk*grgrrgkdk
xcvxcvaokldjfkj*aaadddrkjfk*qqksodjidf
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1234K:4P**0~
;

 

the rows given to us  are 2,6,10 (there are 2060 such rows)

so whenever the row numbers are given, I want to populate the 3rd element with *0*

for example in row 2 in the data have, I want this SVD*H2459*0*HC:93000**1~ (The 3rd element is populated with 0 now).

I want to this for all rows in the given list

 

MY CODE

 

%let list = 2,6,10;

 

data want;
set have;
if _N_ in (&list) then do ;
string= tranwrd(string,"**","*0*");
end;
run;

THE problem is it also changes the other blank in the same row to 0, which i don't want.

 

I also tried the Substr(string,11,1) = '0'; but it didn't help 

I also tried to use the scan function but it doesn't recognizes the blank (**) between stars.

 

Could Someone please help.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

You're most of the way there.  Your program has the right structure to it, and it's only a question of what

goes inside the DO group.  Here's one possibility:

 

found = index(string, '**');

string = substr(string, 1, found) || '0' || substr(string, found+1);

View solution in original post

16 REPLIES 16
Reeza
Super User

Use SCAN to extract the components and then CATX() to recombine. 

athapa1183
Obsidian | Level 7
Hi Reeza, As i mentioned the scan does not recognize the ** as a blank and is skipping to the next element with no blanks.
Kurt_Bremser
Super User

@athapa1183 wrote:
Hi Reeza, As i mentioned the scan does not recognize the ** as a blank and is skipping to the next element with no blanks.

Note the dsd option in @Reeza's code. That's what causes the input statement to recognize two separators as a missing value.

Astounding
PROC Star

You're most of the way there.  Your program has the right structure to it, and it's only a question of what

goes inside the DO group.  Here's one possibility:

 

found = index(string, '**');

string = substr(string, 1, found) || '0' || substr(string, found+1);

athapa1183
Obsidian | Level 7
Thank You Astounding..
Kurt_Bremser
Super User

First of all, I find it extremely sloppy to run logic per row number. One slight change in the dataset, and you end up with garbage.

I'd rather have the logic controlled by the fact that the first "word" in the string is 'SVD'.

 

data want;
set have;
length newstring $150;
if scan(string,1,'*') = 'SVD'
then do;
  do i = 1 to countw(string,'*');
    if i = 3
    then newstring = catx('*',trim(newstring),'0');
    else newstring = catx('*',trim(newstring),scan(string,i,'*'));
  end;
  string = newstring;
end;
drop i newstring;
run;
athapa1183
Obsidian | Level 7
Thank you KurtBremser. There are many SVD that we don't need to change that is why we need to go by the given rows. But your code logic is really helpful.
Kurt_Bremser
Super User

@athapa1183 wrote:
Thank you KurtBremser. There are many SVD that we don't need to change that is why we need to go by the given rows. But your code logic is really helpful.

There has to be some logic which lines need to be changed. Implement that logic right here in that data step, or you will end up footing yourself in the shoot.

gamotte
Rhodochrosite | Level 12

Since the "*" seems to be a separator between the different elements of each row, why don't you use it

to read your data in separate columns ? It makes further processing much simpler.

 

data want;
infile datalines dlm="*" missover dsd;
format var1-var6  $20.;
input var1-var6;

if var3=" " then var3="0";

datalines;
stevdend*kdioror*kdkdkd
SVD*H2459**HC:93000**1~
fakdlkfjajdf*kdkfkldlk*kdkdk
dkfjaokldjfkj*jkdkjfk*kdkkd
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1090F:8P**0~
qwwkfjajdf*grekfkldlk*grgrrgkdk
xcvxcvaokldjfkj*aaadddrkjfk*qqksodjidf
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1234K:4P**0~
;
run;
athapa1183
Obsidian | Level 7
Thank you it seems to work but I need to concatanate all 6 columns at end.
it is really insightful.
gamotte
Rhodochrosite | Level 12

You can concatenate the columns with catx but it won't take missing values into account (ie, you

won't have two consecutive asteriks when a variable is missing).

Otherwise, you can use cat and strip but you have to list each variable. Both cases are illustrated below :

 

options mlogic mprint;

data want;
infile datalines dlm="*" missover dsd;
format var1-var6  $20. var7 $150.;
input var1-var6;

if var3=" " then var3="0";

/* Other operations ... */

var7=catx("*",of var1-var6);
var8=cat(strip(var1),"*",strip(var2),"*", strip(var3),"*", strip(var4),"*",strip(var5),"*",strip(var6));


datalines;
stevdend*kdioror*kdkdkd
SVD*H2459**HC:93000**1~
fakdlkfjajdf*kdkfkldlk*kdkdk
dkfjaokldjfkj*jkdkjfk*kdkkd
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1090F:8P**0~
qwwkfjajdf*grekfkldlk*grgrrgkdk
xcvxcvaokldjfkj*aaadddrkjfk*qqksodjidf
woososdlsld*ldlldkdka*lkswkd
SVD*H2459**HC:1234K:4P**0~
;
run;

With the second method, you have to remove the trailing "***" on the non SVD rows if you want to keep the original structure of rows.

athapa1183
Obsidian | Level 7
it is clear with the cat & strip option.
Thank you
slchen
Lapis Lazuli | Level 10

It is handy with regular expression.

 

data _null_;
  string="SVD*H2459**HC:1090F:8P**0~";
  insert=prxchange('s/\*\*/*0*/',1,string);
  put _all_;
run;

 

athapa1183
Obsidian | Level 7
Thank you So much. But the expression differ.

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
  • 16 replies
  • 1856 views
  • 5 likes
  • 6 in conversation