data test;
input clnt_label $ cd $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
;
run;
I hope to change the format to :
clnt_label cd1 cd2
1234567 740 750
7654321 760 780
In real word, there are a few hundreds of cd codes and millions of records, is there a good way to transform the table as I hope, thanks.
"not transpose though"-- How do i comprehend this? Can you please explain what you mean
Never mind, thanks for you attention. Transpose works fine, I misunderstood it..
proc transpose data=test;
by clnt_label;
var diagcd;
run;
data test;
input clnt_label $ cd $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
;
proc sql noprint;
select max(obs) into :obs
from (select count(*) as obs from test group by clnt_label) ;
quit;
proc summary nway data=test missing;
class clnt_label;
output out = want(drop=_type_ _freq_)
idgroup(out[&obs](cd)=) ;
run;
/*or*/
data test;
input clnt_label $ cd $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
;
proc transpose data=test out=want(drop=_name_) prefix=cd;
by clnt_label;
var cd;
run;
This IS a transposition.
First, build the id variable, then transpose:
data pretrans;
set have;
by clnt_label;
if first.clnt_label
then count = 1;
else count + 1;
run;
proc transpose data=pretrans out=want prefix=cd;
by clnt_label;
id count;
var cd;
run;
Not tested, as I'm on my tablet.
You just need an intermediate step (note: defining a view doesn't create a dataset):
data test;
input clnt_label $ cd $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
;
data temp / view=temp;
do i = 1 by 1 until(last.clnt_label);
set test; by clnt_label notsorted;
output;
end;
run;
proc transpose data=temp out=want(drop=_name_) prefix=cd;
by clnt_label notsorted;
var cd;
id i;
run;
proc print data=want noobs; run;
If you are reading raw data and you always have exactly 2 obs per clnt_label, then :
data want (drop=dummy);
input clnt_label $ cd1 $ / dummy $ cd2 $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
run;
But if you want to convert data set TEST without using proc transpose (but why?), then:
data test;
input clnt_label $ cd $;
datalines;
1234567 740
1234567 750
7654321 760
7654321 780
run;
data want (drop=_:);
do _i=1 by 1 until (last.clnt_label);
set test (rename=(cd=_cd));
by clnt_label notsorted;
array cd {2} $4 ;
cd{_i}=_cd;
end;
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.