Assign Macro Value in Data Step

[sasmaverick]

I have the below dataset.

data test;

infile datalines truncover;

input ID $9. Address $200.;

datalines;

001 Wyoming Avenue 22335 USA

001 Connecticut Avenue 33333 USA

001 France Avenue 55555 USA

001 Wyoming Avenue 22335 USA

001 Connecticut Avenue 33333 USA

001 France Avenue 55555 USA

001 Wyoming Avenue 22335 USA

001 Connecticut Avenue 33333 USA

001 France Avenue 55555 USA

;

run;

data testsort2;

set testsort;

by id address;

if first.address=1 then addrcount=0;

if first.address=0 then do;

addrcount+1;

call symput('addrcount',addrcount);

Address_&addrcount.=address;

end;

run;

In the above code, I want a new Address_addrcount variable to be created when the addrcount changes i.e Address_1, Address_2, Address_3. I need to know how to assign the addrcount value in the same data step. Please help.

[Reeza]

What do you want your output to look like? I don't think macros are the answer to your question.

[sasmaverick]

Let me rephrase the question. I want to dynamically create variables based on the value of addrcount. So if the values of addrcount are 1,2,3 then I want Address_1, Address_2, Address_3 to be created with the corresponding value of Address.

data testsort2;

set testsort;

by id address;

if first.address=1 then addrcount=0;

if first.address=0 then do;

addrcount+1;

call symput('addrcount',addrcount);

Address_&addrcount.=address;

end;

run;

I Want the output to look something like the following:

ID     Address                                             Address_1                                             Address_2                                 

001 Wyoming Avenue 22335 USA        Connecticut Avenue 33333 USA         France Avenue 55555 USA

[Reeza]

Assuming I understand what you want, it might be a proc transpose instead:

data test;

infile datalines truncover;

input ID $3. Address $200.;

datalines;

001 Wyoming Avenue 22335 USA

001 Connecticut Avenue 33333 USA

001 France Avenue 55555 USA

002 Wyoming Avenue 22335 USA

002 Connecticut Avenue 33333 USA

002 France Avenue 55555 USA

003 Wyoming Avenue 22335 USA

003 Connecticut Avenue 33333 USA

003 France Avenue 55555 USA

;

run;

proc transpose data=test out=want prefix=Address;

by ID;

var address;

run;

[sasmaverick]

Proc Transpose did it for me. Thank you Reeza!

[RW9]

Sounds like simple by group processing:

data want;    

     set have;

     by address_1 address_2 address_3;

     retain address_count;

     if _n_=1 then address_count=1;

     else if first.address3 then address_count=address_count+1;

run;

Or you could do:

proc sql;

     create table WANT As

     select     A.*,

                   (select count(1) from HAVE where A.ADDRESS_1=ADDRESS_1 and A.ADDRESS_2=ADDRESS_2 and                                                       A.ADDRESS_3=ADDRESS_2) as RESULT

     from       HAVE A;

quit;