DATA Step, Macro, Functions and more

How to copy cell value to other cell within the group

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How to copy cell value to other cell within the group

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 1Var 2Required
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19Hmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
0000000000000000001800008640326AHmas$ZYcNhrbd30NniiOc6x19
00033F83EF8F58FB6AF3ACA6FFFFFFFFOmrsjLWkNxioc7M1Oj6$bex19OmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFFOmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFFOmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFFOmrsjLWkNxioc7M1Oj6$bex19
00033F83EF8F58FB6AF3ACA6FFFFFFFFOmrsjLWkNxioc7M1Oj6$bex19
001EE2E43C3711D1A0193A08FFFFFFFF90696x22EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFFEU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFF90696x22EU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFFEU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFFEU$GWYSGNx1afdMxcVGQb2x19
001EE2E43C3711D1A0193A08FFFFFFFFEU$GWYSGNx1afdMxcVGQb2x19EU$GWYSGNx1afdMxcVGQb2x19
9FB83E1BFC68D743250F4D9AFFFFFFFF05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF05qOOZmsNxha0cM1Oj6$bex19
9FB83E1BFC68D743250F4D9AFFFFFFFF05qOOZmsNxha0cM1Oj6$bex19
A0012E3CA47467275AD0EC12FFFFFFFF05qOOZmsNxha0cM1Oj6$bex1905qOOZmsNxha0cM1Oj6$bex19

Accepted Solutions
Solution
‎10-21-2013 11:28 AM
Super Contributor
Posts: 543

Re: How to copy cell value to other cell within the group

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.

View solution in original post


All Replies
Solution
‎10-21-2013 11:28 AM
Super Contributor
Posts: 543

Re: How to copy cell value to other cell within the group

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.

Contributor
Posts: 22

Re: How to copy cell value to other cell within the group

Posted in reply to AncaTilea

Thank you Anca telia,

Your code is working perfect! Smiley Happy

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

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;

🔒 This topic is solved and locked.

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

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