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.
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.