How to copy cell value to other cell within the group

[SasPro]

Hi,

I have 2 variables var1 and var2, Var1 is a grouping variable and var2 is reference variable. Here i want to create new variable called Required which takes values from Var2 from the group(var1) and should copy to new variable (Required).

I tried with first. and last. My logic is not working for this data. as u can see then values may lie middle of the group.

And in the third group you can see we have 2 values 90696x22 and GWYSGNx1afdMxcVGQb2x19, in this case i want to GWYSGNx1afdMxcVGQb2x19 to new variable. Hint is i want to copy only those values from var2 who's value is ending with "x19".

It will be great full for your support.

Thanks

Var 1	Var 2	Required

0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A	Hmas$ZYcNhrbd30NniiOc6x19	Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A	Hmas$ZYcNhrbd30NniiOc6x19	Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326A		Hmas$ZYcNhrbd30NniiOc6x19
00033F83EF8F58FB6AF3ACA6FFFFFFFF	OmrsjLWkNxioc7M1Oj6$bex19	OmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFF		OmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFF		OmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFF		OmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFF		OmrsjLWkNxioc7M1Oj6$bex19
001EE2E43C3711D1A0193A08FFFFFFFF	90696x22	EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF		EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF	90696x22	EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF		EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF		EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF	EU$GWYSGNx1afdMxcVGQb2x19	EU$GWYSGNx1afdMxcVGQb2x19
9FB83E1BFC68D743250F4D9AFFFFFFFF		05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF		05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF		05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF		05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF		05qOOZmsNxha0cM1Oj6$bex19
A0012E3CA47467275AD0EC12FFFFFFFF	05qOOZmsNxha0cM1Oj6$bex19	05qOOZmsNxha0cM1Oj6$bex19

[AncaTilea · Accepted solution]

Hi SasPro.

I wrote a simple code, but the results are slightly different.

*sort original data by var1 and var2;

proc sort data = have;by var1 var2;run;

*create a temp file that flags the observations that have the "19" in your var2;

data temp(keep = var1 var2 rename = var2 = required);

    set have;

    by var1 var2;

    flag = ((scan(left(var2),-1,"x")) = "19" );

    if flag = 1;

    if first.var2;

run;

*merge it back together;

data want;

    merge have temp;

    by var1;

run;

As you can see, my final data set doesn't copy var2 info for the last record"A0012E3CA47467275AD0EC12FFFFFFFF" into the second to last record "A0012E3CA47467275AD0EC12FFFFFFFF"

(Not sure how you decide that the info from the last record should be populated into the second to last?)

Good luck.

Anca.

[SasPro]

Thank you Anca telia,

Your code is working perfect!

I tried to execute with other logic it is also working file

proc sort data = have;by var1 descending

var2;run;

data temp;

set have;

by var1 ;

if first.var1 then output;

Run;

data want;

    merge have temp;

    by var1;

run;