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.
What do you want your output to look like? I don't think macros are the answer to your question.
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
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;
Proc Transpose did it for me. Thank you Reeza!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.