This is my dataset:
Client | Gender | Test_ID | Test_Date |
A | F | 123 | 5/1/2015 |
A | F | 567 | 11/1/2015 |
B | M | 789 | 1/1/2017 |
B | M | 548 | 6/1/2017 |
C | M | 121 | 8/1/2016 |
C | M | 540 | 2/1/2017 |
I want this dataset:
Client | Gender | Test_ID_one | Test_ID_two | Test_Date_one | Test_date_two |
A | F | 123 | 567 | 5/1/2015 | 11/1/2015 |
B | M | 789 | 548 | 1/1/2017 | 6/1/2017 |
C | M | 121 | 540 | 8/1/2016 | 2/1/2017 |
I think this is a transpose? Any help would be great!
Does each client level always only contain two observations?
in my current dataset, yes - there are only 2 observations per client. But, I may expand that to multiple obs per client (not exceeding 25 obs)
Yes, PROC TRANSPOSE is the tool. I did a simple google search
transpose site:sas.com
and the first hit had an example of what you are looking for
https://support.sas.com/resources/papers/proceedings09/060-2009.pdf
May require a double transpose and the transpose will likely convert the dates to character
If you are OK with this, I can try
data have;
input Client $ Gender $ test_id $ Test_Date :mmddyy10.;
format test_date mmddyy10.;
cards;
A F 123 5/1/2015
A F 567 11/1/2015
B M 789 1/1/2017
B M 548 6/1/2017
C M 121 8/1/2016
C M 540 2/1/2017
;
data want;
do _n_=1 by 1 until(last.client);
set have;
by Client ;
array j(*)$ test_id1-test_id2 ;
array k(*) Test_Date1-Test_Date2;
if _n_=1 then call missing(of j(*),of k(*));
j(_n_)=test_id;
k(_n_)=Test_Date;
end;
format test_Date: mmddyy10.;
drop test_id test_date;
run;
So lets look at what @kt_080517 has where they may have multipole test for a client how would they make test_id2 and test_date2 dynamic so they would not have to edit the program and replace the test_id2 with test_id3?
data have;
input Client $ Gender $ test_id $ Test_Date :mmddyy10.;
format test_date mmddyy10.;
cards;
A F 123 5/1/2015
A F 124 5/4/2015
A F 567 11/1/2015
B M 789 1/1/2017
B M 790 2/21/2017
B M 548 6/1/2017
C M 121 8/1/2016
C M 540 2/1/2017
;
data want;
do _n_=1 by 1 until(last.client);
set have;
by Client ;
array j(*)$ test_id1-test_id3 ;
array k(*) Test_Date1-Test_Date3;
if _n_=1 then call missing(of j(*),of k(*));
j(_n_)=test_id;
k(_n_)=Test_Date;
end;
format test_Date: mmddyy10.;
drop test_id test_date;
run;
Sorry, what do you mean by this So lets look at what kt_080517 has where??
I am not catching your suggestion
Can Proc SQL be used here? to bring a value from obs other than the first into a column of its own so all data is in one row?
why sql?
Sql can help in getting the array subscript(number of elements) as a macro to make the code i shared dynamic. however, for transpose either data-step or proc transpose is best
So this should handle multiple id requirement:
data have;
input Client $ Gender $ test_id $ Test_Date :mmddyy10.;
format test_date mmddyy10.;
cards;
A F 123 5/1/2015
A F 567 11/1/2015
B M 789 1/1/2017
B M 548 6/1/2017
C M 121 8/1/2016
C M 540 2/1/2017
;
proc sql;
select max(cnt) into : m trimmed
from (select count(client) as cnt from have group by client);
quit;
data want;
do _n_=1 by 1 until(last.client);
set have(rename=(test_id=_test_id Test_Date=_Test_Date));
by Client ;
array test_id(&m)$ ;
array Test_Date(&m);
if _n_=1 then call missing(of test_id(*),of Test_Date(*));
test_id(_n_)=_test_id;
Test_Date(_n_)=_Test_Date;
end;
format test_Date: mmddyy10.;
drop _:;
run;
@kt_080517 wrote:
This is my dataset:
Client Gender Test_ID Test_Date A F 123 5/1/2015 A F 567 11/1/2015 B M 789 1/1/2017 B M 548 6/1/2017 C M 121 8/1/2016 C M 540 2/1/2017
I want this dataset:
Client Gender Test_ID_one Test_ID_two Test_Date_one Test_date_two A F 123 567 5/1/2015 11/1/2015 B M 789 548 1/1/2017 6/1/2017 C M 121 540 8/1/2016 2/1/2017
I think this is a transpose? Any help would be great!
Another of these combine rows question and I just spent yet another couple of hours or so undoing data provided in that format so it can actually be used...
Repeat after me: I hate spreadsheets.
data have;
input Client $ Gender $ test_id $ Test_Date :mmddyy10.;
format test_date mmddyy10.;
cards;
A F 123 5/1/2015
A F 567 11/1/2015
B M 789 1/1/2017
B M 548 6/1/2017
C M 121 8/1/2016
C M 540 2/1/2017
;
proc summary data=have nway;
class Client ;
output out=want idgroup(out[2] ( Gender test_id Test_Date)=);
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.