re: assign new value

[twildone]

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

[Astounding · Accepted solution]

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;

[twildone]

Hi Astounding.....it worked perfectly...thanks for your help.