topic Re: Assign Macro Value in Data Step in SAS Programming

Assign Macro Value in Data Step

sasmaverick — Wed, 04 Feb 2015 15:45:38 GMT

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.

Re: Assign Macro Value in Data Step

Reeza — Wed, 04 Feb 2015 15:49:16 GMT

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

Re: Assign Macro Value in Data Step

RW9 — Wed, 04 Feb 2015 16:06:42 GMT

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;

Re: Assign Macro Value in Data Step

sasmaverick — Wed, 04 Feb 2015 16:34:10 GMT

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

Re: Assign Macro Value in Data Step

Reeza — Wed, 04 Feb 2015 16:53:47 GMT

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;

Re: Assign Macro Value in Data Step

sasmaverick — Wed, 04 Feb 2015 20:31:26 GMT

Proc Transpose did it for me. Thank you Reeza!