Create a string

[chennupriya]

Hi ,

I have a sas dataset billplan which has below three rows in the dataset and i want to loop through
those 3 rows for boap1 , boap2 and boap3 variables and create a string

first three variables of row1 are seperated by * and then | then two variables of row2 are seperated by * and then |
and one variable of row3 and loop ends

 finally state1 should resolve to 

    Ins*Status*Cycle|Total*Pays|Cust

Table BillPLan

SName	tst	boap1	boap2	boap3
Score	h	Ins	Status	Cycle
Sex	h	Total	Pays
Age	h	Cust
Height	h	Num	Disc	Early

Can anyone please help ?

[Astounding]

This seems like it should work:

data want;

set billplan end=done;

length this_line $ 50 state1 $ 100;

retain state1;

this_line = catx('*', boap1, boap2, boap3);

state1 = catx('|', state1, this_line);

if done;

run;

Still, unless you tell us more it is difficult to accept the usefulness of this result.

[chennupriya]

proc sql;

select

%do i=1 %to 4;

%do j=1 %to 3;

select upcase(trim(boap&j)) as boap&j

%if &j < 3 %then %do;

"*"

%end;

%end;

into :state1 seperated by "|"

from billplan;

quit;

So number of rows is 4 so first time it loops 4 times and then it loops three times for  three variables all three variables are separated by * and each row is separated by |and that's how the variable State1 at the end looks like

Ins*Status*Cycle|Total*Pays |Cust |Num*Disc*Early

[Astounding]

Did you try my program?  To me, it looks much simpler.  Do you require the final string to be placed into a macro variable?  A DATA step can easily do that.

[chennupriya]

Hi Above code works but what if I don't know the length of the state1(length of final string )  since I have 300 rows and each row has 4 variables ? and the string would be truncated if we don't give correct length

Please help

[ballardw]

---

@chennupriya wrote:

Hi Above code works but what if I don't know the length of the state1(length of final string )  since I have 300 rows and each row has 4 variables ? and the string would be truncated if we don't give correct length

Please help

---

Know thy data.

You would need to examine the number of variables and their lengths that will concatenated. For ease of calculations you can use the length(s) assigned to the variables, sum those and add one character per variable per "row" you might need to provide for a space or comma or what ever between values. Then assign that as the length of the variable.

If you want an "automagic" code for such start be examining sashelp.vcolumn (or dictionary.columns if you use Proc SQL) to pull the lengths of the variables. SASHELP.Vtable (or dictionary.tables ) has the number of observations in the variable NOBS.

Or use proc contents to get info.

[Astounding]

Just give a long enough length, such as:

length state1 $ 20000;

Also use the global option COMPRESS=YES so that if you assign extra space, that extra space won't be needed for storing the value.

If you transfer the value to a macro variable, use CALL SYMPUTX, not CALL SYMPUT.  That way, the extra blanks won't be transferred.