topic Change the table format, not transpose though. in SAS Programming

Change the table format, not transpose though.

pensarchem — Thu, 14 Nov 2019 21:06:46 GMT

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.

Re: Change the table format, not transpose though.

novinosrin — Thu, 14 Nov 2019 21:12:16 GMT

"not transpose though"-- How do i comprehend this? Can you please explain what you mean

Re: Change the table format, not transpose though.

pensarchem — Thu, 14 Nov 2019 21:16:35 GMT

Never mind, thanks for you attention. Transpose works fine, I misunderstood it..

proc transpose data=test;

by clnt_label;

var diagcd;

run;

Re: Change the table format, not transpose though.

novinosrin — Thu, 14 Nov 2019 21:18:55 GMT


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;

Re: Change the table format, not transpose though.

Kurt_Bremser — Thu, 14 Nov 2019 21:20:08 GMT

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.

Re: Change the table format, not transpose though.

PGStats — Thu, 14 Nov 2019 21:20:15 GMT

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;

Re: Change the table format, not transpose though.

mkeintz — Thu, 14 Nov 2019 21:25:14 GMT

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;