DATA Step, Macro, Functions and more

Splitting record into multiple records

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Splitting record into multiple records

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

DateNameID1Description1ID2Description2
29-Apr-17Claire123100. 
04-Apr-17David456200555300
05-Apr-17David456200555300
06-Apr-17David456200555300

06-Apr-17

David456200555300
24-Apr-17David456200555300
25-Apr-17David456200555300
25-Apr-17David456200555300
26-Apr-17David456200555300
26-Apr-17David456200555300
27-Apr-17David456200555300

 

 

Want:

 

DateNameID1Description1
29-Apr-17Claire123100
04-Apr-17David456200
05-Apr-17David456200
06-Apr-17David456200
06-Apr-17David456200
24-Apr-17David456200
25-Apr-17David456200
25-Apr-17David456200
26-Apr-17David456200
26-Apr-17David456200
27-Apr-17David456200
04-Apr-17David555300
05-Apr-17David555300
06-Apr-17David555300
06-Apr-17David555300
24-Apr-17David555300
25-Apr-17David555300
25-Apr-17David555300
26-Apr-17David555300
26-Apr-17David555300
27-Apr-17David555300

 

Thanks in advance.


Accepted Solutions
Solution
4 weeks ago
Super User
Super User
Posts: 9,866

Re: Splitting record into multiple records

Posted in reply to westwood49

Should be straightforward:

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

View solution in original post


All Replies
Solution
4 weeks ago
Super User
Super User
Posts: 9,866

Re: Splitting record into multiple records

Posted in reply to westwood49

Should be straightforward:

data want (drop=id1 id2 description1 description2);
  set have;
  id=id1;
  description=description1;
  output;
  id=id2;
  description=description2;
  output;
run;
New Contributor
Posts: 2

Re: Splitting record into multiple records

Worked perfectly, thank you so much.

Super User
Posts: 10,617

Re: Splitting record into multiple records

Posted in reply to westwood49

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,935

Re: Splitting record into multiple records

Posted in reply to westwood49

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;

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 72 views
  • 0 likes
  • 4 in conversation