topic Re: Copy the column values of a row to another row based on a specific condition in SAS Programming

Copy the column values of a row to another row based on a specific condition

SP01 — Sat, 25 Nov 2023 15:12:18 GMT

I want to copy the column values of a row to another row based on some condition using the SAS data step or Proc SQL. I have the following data:

can_id	date_1	field_1	field_2	field_3
1	11/1/2022	char	char
1	10/2/2023	far	char
2	11/28/2022	teg	meg	geg
3	12/1/2022	ham	hjk	sop
3	12/1/2022	ham	kli	iop
4	1/2/2023	jsk	kli	lom

I want to copy date_1, field_1, field_2, and field_3 from the first row of the same can_id to any subsequent rows that have the same can_id.

The result I am trying to achieve looks like this:

can_id	date_1	field_1	field_2	field_3
1	11/1/2022	char	char
1	10/2/2023	char	char
2	11/28/2022	teg	meg	geg
3	12/1/2022	ham	hjk	sop
3	12/1/2022	ham	hjk	sop
4	1/2/2023	ham	hjk	sop

Re: Copy the column values of a row to another row based on a specific condition

PaigeMiller — Sat, 25 Nov 2023 16:28:45 GMT

UNTESTED CODE

data want;
    set have;
    by can_id;
    retain field_1a field_2a field_3a;
    if first.can_id then do;   
        field1a=field_1;
        field2a=field_2;
        field3a=field_3;
    end;
    drop field_1 field_2 field_3;
run;

Re: Copy the column values of a row to another row based on a specific condition

Patrick — Sat, 25 Nov 2023 23:24:58 GMT

Is below returning what you're looking for?

data have;
  infile datalines dsd truncover;
  input can_id date_1:mmddyy. (field_1 field_2 field_3) (:$10.);
  format date_1 date9.;
  datalines;
1,11/1/2022,char,char, 
1,10/2/2023,far,char, 
2,11/28/2022,teg,meg,geg
3,12/1/2022,ham,hjk,sop
3,12/1/2022,ham,kli,iop
4,1/2/2023,jsk,kli,lom
;

data v_inter/view=v_inter;
  set have(keep=can_id field_1-field_3);
  by can_id;
  if first.can_id;
run;

data want;
  merge have(drop=field_1-field_3) v_inter;
  by can_id;
run;

proc print data=want;
run;

Re: Copy the column values of a row to another row based on a specific condition

s_lassen — Mon, 27 Nov 2023 07:35:39 GMT

Another possibility is to read the dataset twice in the same data step, e.g.:

data want;
  set have;
  by can_id;
  if first.can_id then do until(last.can_id2);
    set have(rename=(can_id=can_id2) drop=field_1-field_3);
    by can_id2;
    output;
    end;
  drop can_id2;
run;