hi i have :
ID Date counter prefix
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
i want
ID newvar1 newvar2
101 date1 date5
101 date2 date6
101 date3 date7
101 . date8
Thanks
If you want to use proc transpose, I would suggest something like:
data have;
input ID Date $ counter prefix $;
cards;
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
;
proc sort data=have;
by id counter;
run;
proc format;
value $prefix
'out'=1
'in'=2
;
run;
proc transpose data=have
out=want (drop=_name_ counter)
prefix=newvar;
by id counter;
var date;
id prefix;
format prefix $prefix.;
run;
from what you showed here, it seems NOT a transpose question. You can use ' lag3' to solve your problem.
data transpose1;
input ID Date $ counter prefix;
cards;
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
run;
data transpose2;
set transpose1;
date5 = lag3(date);
run;
But the other ID's may not have the exact scenario..they might have less or more counters!!!
Could you help in that scenario also???
Thanks
show me more scenarios.
data t1;
input ID Date $ counter prefix $;
cards;
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
;
run;
proc sort data = t1 out = t2;
by id prefix;
run;
proc transpose data=t2 out= t3 prefix = prefix;
by id prefix;
id counter;
var date;
run;
proc transpose data=t3 out= t4 prefix=prefix ;
by id;
var prefix1 - prefix4;
run;
If you want to use proc transpose, I would suggest something like:
data have;
input ID Date $ counter prefix $;
cards;
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
;
proc sort data=have;
by id counter;
run;
proc format;
value $prefix
'out'=1
'in'=2
;
run;
proc transpose data=have
out=want (drop=_name_ counter)
prefix=newvar;
by id counter;
var date;
id prefix;
format prefix $prefix.;
run;
Thanks so very much. It works well.But is there any other method other than the transpose??which is the beest on eto go for in such situations??
Thanks
Given data like your test data, I'd think that the proc transpose solution would require the least upkeep for the code.
FWIW, Here is a Hash() approach,
data have;
input ID Date $ counter prefix $;
cards;
101 date1 1 out
101 date2 2 out
101 date3 3 out
101 date5 1 in
101 date6 2 in
101 date7 3 in
101 date8 4 in
;
data _null_;
if _n_=1 then do;
if 0 then set have;
length newvar1 newvar2 $ 8;
declare hash h(ordered: 'y');
h.definekey('id','counter');
h.definedata('id', 'newvar1', 'newvar2');
h.definedone();
end;
set have end=last;
rc=h.find();
if prefix='out' then newvar1=date;
else if prefix='in' then newvar2=date;
rc=h.replace();
if last then h.output(dataset:'want');
run;
Haikuo
Or using self-merge skill:
data have; input ID Date $ counter prefix $; cards; 101 date1 1 out 101 date2 2 out 101 date3 3 out 101 date5 1 in 101 date6 2 in 101 date7 3 in 101 date8 4 in ; run; data want(drop=prefix); merge have(keep=id date prefix rename=(date=date1) where=(prefix='out')) have(keep=id date prefix rename=(date=date2) where=(prefix='in')); by id; output; call missing(of _all_); run;
Dr. Arthur.T , glad to see you , How is SGF2013 being ?
Ksharp
: SGF was fantastic! Our transpose paper was extremely well received, had almost 200 attendees, and I've been asked to present a 50 minute version this September at MWSUG as one of their featured presentations. I didn't mention it in this thread as I didn't think it would help the OP but, if anyone is interested in knowing how to speed up transpositions up to 50 times faster than one can with proc transpose, take a look at:
http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
Dr Arthur.T,
Nice. glad to see you continue to make contributions for SAS users.
Ksharp
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: