topic Concatenate a variable within a loop in SAS Procedures

Concatenate a variable within a loop

gsnidow — Tue, 03 Apr 2012 13:04:47 GMT

Greetings. I'm an absolute SAS newbie, so I'll do the best I can with describing my problem. I need to be able to take a variable, say 'x' and re-set it's value to it's existing value concatenated with a substring of another variable, for each iteration of the loop. So, lets say I have a variable named var with value '4D0000001204E6', and I have 'x' whose initial value is '' or null. I need to have a loop iterate for i = 1 to length(var)-1 by 2, and set x = x + substr(var,i,2). So, for this example, my first iteration would set x = '4D', since i = 1. The next iteration would need to set x = '4D' + '00', since i now = 3. Next iteration would set x = '4D00' + '00' for i = 5, etc until I have rebuilt the initial string (var). Now, I realize this may seem a waste since I already know the value of var, but this is just a small piece. I will be using the substrings to match an EBCDIC_ASCII lookup table, and will actually be returning the EBCDIC value where the ASCII value = substr(var,1,2). That is a whole other issue. For now I just would like to re-create var. So far, this is what I have tried, to no avail...

%Let var = '4D0000001204E6';

data test;

x = '';

do i = 1 to length(&var.)-1;

x = x||trim(substr(&var.,i,2));

end;

run;

Thank you all for any help you might be willing to give to a newbie.

Greg

Re: Concatenate a variable within a loop

Astounding — Tue, 03 Apr 2012 13:15:55 GMT

Greg,

You've got a lot of the right pieces in place. A few tweaks should do it.

First, assign a length to X. In place of x='';, use:

length x $ 26;

At least 26 looks like the right length based on your description.

Then inside the loop, change the assignment to:

x = trim(x) || substr(&var., i, 2);

That should be all it takes.

Good luck.

Ooppss ... upon rereading the problem,

You might need to add to the DO loop:

BY 2;

And the length of 14 might be sufficient.

Re: Concatenate a variable within a loop

Linlin — Tue, 03 Apr 2012 13:26:29 GMT

How about:

%Let var=4D0000001204E6;

data test;

length x $ 20;

retain x;

do _n_=1 to length("&var.") by 2;

y=substr("&var.",_n_,2);

x=cats(x,y);

output;

end;

proc print;run;

Obs x y

1 4D 4D

2 4D00 00

3 4D0000 00

4 4D000000 00

5 4D00000012 12

6 4D0000001204 04

7 4D0000001204E6 E6

If you only need the last observation, then just take out the output statment.

Linlin

Concatenate a variable within a loop

gsnidow — Tue, 03 Apr 2012 14:41:20 GMT

Thank you both so much for taking your good time to consider my problem.

Linlin, that is exactly what I am trying to do. I have a couple of questions if you would be so kind as to enlighten me. Is _n_ some kind of internal row variable, that keeps track of the current iteration? Also, why the double quotes around &var.?

Concatenate a variable within a loop

Linlin — Tue, 03 Apr 2012 15:04:39 GMT

Hi Greq,

Welcome to the Forum! _n_ is an automatic variable sas created. In your example, we don't have to use _n_, if we use "i" or some other letters, they will stay in the final dataset as a variable, we need to drop it. When you use a macro variable outsite a macro definition, you have to use double quotes (I maybe wrong about this) .

the code below shows the difference to replace _n_ with other letters:

%Let var=4D0000001204E6;

data test;

length x $ 20;

retain x;

do i=1 to length("&var.") by 2;

y=substr("&var.",i,2);

x=cats(x,y);

output;

end;

proc print;run;

Obs x i y

1 4D 1 4D

2 4D00 3 00

3 4D0000 5 00

4 4D000000 7 00

5 4D00000012 9 12

6 4D0000001204 11 04

7 4D0000001204E6 13 E6

If you don’t want i in final dataset, you need to drop it:

%Let var=4D0000001204E6;

data test (drop=i) ;

length x $ 20;

retain x;

do i=1 to length("&var.") by 2;

y=substr("&var.",i,2);

x=cats(x,y);

output;

end;

proc print;run;

Concatenate a variable within a loop

Astounding — Tue, 03 Apr 2012 15:10:55 GMT

Greg,

In your original %LET statement, you added quotes. But in Linlin's example, his %LET statement omitted the quotes. So the DATA step added them later (using double quotes instead of single, because single quotes would prevent resolution of &VAR.).

Concatenate a variable within a loop

gsnidow — Tue, 03 Apr 2012 15:15:52 GMT

Astounding, my entire thought process is SQL Server and T-SQL, so I am coming from a world where every string variable needs to have single quotes. I am quickly realizing I should forget about SQL rules in SAS (except, of course, when I am using proc sql). At this point it is trial and error, but to your point, I did see where the single quotes were being treated as part of the string, so I removed them. Thanks again for your comments.

Greg

Concatenate a variable within a loop

gsnidow — Tue, 03 Apr 2012 16:56:57 GMT

Linlin, maybe I should have started this way from the get go, but I am finding that when I try to apply the logic above to a column, x does not reset for each observation. My though was that since I know I only want to get substrings up to length(variable)-1, I could just change it to length(variable), then use an if statement to see whether or not _n_ was equal to the length. If equal to the length, then reset x for the next observation. What I am seeing, though, is that _n_ does not seem to ever get to the length(variable), always stays 1 less. So what is happening is that for each observation, x is retaining it's value from the last one. I also tried retaining an id variable, then using if first.id then... but that did not work either. Do you see what I am doing wrong?

data test;

input id value=$156.;

datalines;

1 value=4D0000001204E6

2 value=4400000097CA5F4D00000009CA14

3 value=20

4 value=4D000000036A3C

;

data test;

set test;

length x $ 156;

retain x;

do _n_=1 to length(value) by 2;

if _n_ < length(value) then do;

put "_n_ = " _n_;

y=substr(value,_n_,2);

x=trim(cats(x,y));

end;

if _n_ = length(value) then do; *_n_ does not seem to ever reach the length of the value;

put "_n_ = length";

x=""; *how do I put logic to reset x?;

end;

drop y;

run;

Concatenate a variable within a loop

Astounding — Tue, 03 Apr 2012 17:33:28 GMT

Greg,

The culprit is the RETAIN statement. Just get rid of it, and X will start out blank for each new observation. Once you do that, you should also remove the 4 statements you added, to try to reset X.

Also, a small short-cut ... Replace this:

put "_n_ = " _n_;

Use this instead:

put _n_=;

Concatenate a variable within a loop

gsnidow — Tue, 03 Apr 2012 18:16:47 GMT

Thank you Astounding. You are indeed correct. I read about retain, and it seemed to make perfect sense why we needed it at first. However, if I am understanding what I read correctly, retain will keep a variables value across observations, so in this case it is thwarting me. Another reason why I probably should have stated what I wanted to do in the first place. Again, thank you both so much for your time and effort.

Greg

This works...

data test;

set test;

length x $ 156;

do _n_=1 to length(value)-1 by 2;

y=substr(value,_n_,2);

x=trim(cats(x,y));

end;

drop y;

run;

Concatenate a variable within a loop

Astounding — Tue, 03 Apr 2012 18:32:49 GMT

Greg,

Happy to help. As long as you are reading up on the details, here are a couple of items to consider. They may not be needed in this particular application.

First, the length of Y will be 156. When SUBSTR creates a new variable, the new variable's length is the same as that of the incoming string.

Second, SUBSTR can appear on the left-hand side of an assignment statement. This would be legal:

substr(x, _n_, 2) = Y;

Or simply eliminate the creation of Y:

substr(x, _n_, 2) = substr(value, _n_, 2);

This would become important if it were possible for VALUE to contain embedded blanks. (I know that won't happen here where you have hex codes.) In that case, the CATS function would remove them.

Good luck.