Hi...I am trying to create a new variable ID1 based on variable ID. I want to assign the ID value where the status is 'Y' for the same address. The addresses with a status 'Y' represent the current ID for that particular address and there will only be 1 address with a status with a 'Y' and those with a status 'N' are previous and old ID. I can't seem to get my code to work. Thanks
proc sort data=list1;
by address_1 status descending;
run;
data list2;
set list1;
by address_1;
if first.address_1 then
ID1= ID;
else
ID1=lag(ID);
run;
Output:
Address_1 | Status | ID | ID1 |
BOX 1700 34 FIRST ST SW | Y | P820 | P820 |
#26 - 1313 BORDER STREET | Y | P535 | P535 |
1 217 5TH AVE NE | Y | P887 | P887 |
10 414 WESTMOUNT DR | Y | P282 | P282 |
10 414 WESTMOUNT DR | N | P480 | P282 |
10 TERRACON PLACE | Y | P902 | P902 |
100 1155 CONCORDIA AVE | Y | P584 | P584 |
100 1155 CONCORDIA AVE | N | P540 | P584 |
100 235 VERMILLION ROAD | Y | P562 | P562 |
100 2385 PEMBIN HWY | Y | P357 | P357 |
100 2385 PEMBIN HWY | N | P822 | P357 |
100 2385 PEMBIN HWY | N | P463 | P357 |
100 286 SMITH ST | Y | P477 | P477 |
100 BROWNING BLVD | Y | P031 | P031 |
100 BROWNING BLVD | Y | P899 | P031 |
Since you are able to sort your data, the rest of the code would be simple. Note that DESCENDING comes before the variable name, so:
proc sort data=list1;
by address_1 descending status;
run;
data want;
set list1;
by address_1;
if first.address_1 then id1=id;
retain id1;
run;
Since you are able to sort your data, the rest of the code would be simple. Note that DESCENDING comes before the variable name, so:
proc sort data=list1;
by address_1 descending status;
run;
data want;
set list1;
by address_1;
if first.address_1 then id1=id;
retain id1;
run;
Hi Astounding.....it worked perfectly...thanks for your help.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.