DATA Step, Macro, Functions and more

re: assign new value

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

re: assign new value

[ Edited ]

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

Accepted Solutions
Solution
‎04-06-2017 11:47 AM
Super User
Posts: 5,499

Re: re: assign new value

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;

View solution in original post


All Replies
Solution
‎04-06-2017 11:47 AM
Super User
Posts: 5,499

Re: re: assign new value

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;

Regular Contributor
Posts: 229

Re: re: assign new value

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 116 views
  • 0 likes
  • 2 in conversation