topic Splitting record into multiple records in SAS Programming

Splitting record into multiple records

westwood49 — Thu, 26 Jul 2018 07:35:02 GMT

I have a set of data similar to below. I want to move the last two variables down underneath ID1 and Description1 and retain the first variable.

Have

Date	Name	ID1	Description1	ID2	Description2
29-Apr-17	Claire	123	100	.
04-Apr-17	David	456	200	555	300
05-Apr-17	David	456	200	555	300
06-Apr-17	David	456	200	555	300
06-Apr-17	David	456	200	555	300
24-Apr-17	David	456	200	555	300
25-Apr-17	David	456	200	555	300
25-Apr-17	David	456	200	555	300
26-Apr-17	David	456	200	555	300
26-Apr-17	David	456	200	555	300
27-Apr-17	David	456	200	555	300

Want:

Date	Name	ID1	Description1
29-Apr-17	Claire	123	100
04-Apr-17	David	456	200
05-Apr-17	David	456	200
06-Apr-17	David	456	200
06-Apr-17	David	456	200
24-Apr-17	David	456	200
25-Apr-17	David	456	200
25-Apr-17	David	456	200
26-Apr-17	David	456	200
26-Apr-17	David	456	200
27-Apr-17	David	456	200
04-Apr-17	David	555	300
05-Apr-17	David	555	300
06-Apr-17	David	555	300
06-Apr-17	David	555	300
24-Apr-17	David	555	300
25-Apr-17	David	555	300
25-Apr-17	David	555	300
26-Apr-17	David	555	300
26-Apr-17	David	555	300
27-Apr-17	David	555	300

Thanks in advance.

Re: Splitting record into multiple records

RW9 — Thu, 26 Jul 2018 07:47:42 GMT

Should be straightforward:

data want (drop=id1 id2 description1 description2);
  set have;
  id=id1;
  description=description1;
  output;
  id=id2;
  description=description2;
  output;
run;

Re: Splitting record into multiple records

Kurt_Bremser — Thu, 26 Jul 2018 07:57:02 GMT

A less deterministic solution uses a double transpose:

data have;
infile cards truncover dlm='09'x;
input Date :date9. Name $ ID1 Description1 ID2 Description2;
format date yymmddd10.;
record_id = _n_; * we need this for the tranposes;
cards;
29-Apr-17	Claire	123	100	.	 
04-Apr-17	David	456	200	555	300
05-Apr-17	David	456	200	555	300
06-Apr-17	David	456	200	555	300
06-Apr-17	David	456	200	555	300
24-Apr-17	David	456	200	555	300
25-Apr-17	David	456	200	555	300
25-Apr-17	David	456	200	555	300
26-Apr-17	David	456	200	555	300
26-Apr-17	David	456	200	555	300
27-Apr-17	David	456	200	555	300
run;

proc transpose data=have out=int1;
by record_id date name;
var id: desc:;
run;

data int2;
set int1;
sub_id = substr(_name_,length(_name_));
_name_ = substr(_name_,1,length(_name_)-1);
run;

proc sort data=int2;
by record_id sub_id;
run;

proc transpose data=int2 out=want (drop=record_id _name_);
by record_id date name sub_id;
var col1;
id _name_;
run;

Re: Splitting record into multiple records

Astounding — Thu, 26 Jul 2018 08:04:47 GMT

Since you requested that the original variable names (ID1 and Description1) remain:

data want;

set have;

output;

if ID2 > . then do;

id1 = id2;

description1 = description2;

output;

end;

keep date name id1 description1;

run;

Re: Splitting record into multiple records

westwood49 — Thu, 26 Jul 2018 08:07:16 GMT

Worked perfectly, thank you so much.