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.
Should be straightforward:
data want (drop=id1 id2 description1 description2); set have; id=id1; description=description1; output; id=id2; description=description2; output; run;
Should be straightforward:
data want (drop=id1 id2 description1 description2); set have; id=id1; description=description1; output; id=id2; description=description2; output; run;
Worked perfectly, thank you so much.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.