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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.