topic Re: How to copy multiple variables with transpose in SAS Programming

How to copy multiple variables with transpose

SAShole — Fri, 28 Dec 2018 16:21:58 GMT

I have a dataset that looks like this:

data have;
	input block type $ count;
	datalines;
2 A 10
6 B 15
6 C 5
8 D 25
8 E 32
9 E 12
;
run;

and i want to transform it like this:

data want;
	infile datalines missover;
	input block type $ count type2 $ count2;
	datalines;
2 A 10
6 B 15 C 5
8 D 25 E 32
9 E 12
;
run;

In actuality my data has about 6M rows and can have up to 7 'types' for a given block.

Re: How to copy multiple variables with transpose

novinosrin — Fri, 28 Dec 2018 16:41:47 GMT

data have;
	input block type $ count;
	datalines;
2 A 10
6 B 15
6 C 5
8 D 25
8 E 32
9 E 12
;
run;

data _h;
do n=1 by 1 until(last.block);
set have;
by block;
vn=cats(vname(type),n);
v=type;
output;
vn=cats(vname(count),n);
v=put(count,8. -l);
output;
end;
keep block vn v;
run;
proc transpose data=_h out=want(drop=_name_);
by block;
id vn;
var v ;
run;

Re: How to copy multiple variables with transpose

Cynthia_sas — Fri, 28 Dec 2018 16:50:20 GMT

Hi:

Another alternative, if you're going to have a DATA step and a PROC TRANSPOSE is to skip making 2 passes through the data (especially, if it's large data) and just have 1 DATA step using ARRAYs to transpose the data. If there is a possibility of a max of 7 for a block, that makes it easy to define the size of the ARRAY and this approach only makes 1 pass through the data.

This does assume, however, that the data are sorted by BLOCK.

Cynthia


options missing=.;
data want(keep=block bcnt typ1 cnt1 typ2 cnt2 typ3 cnt3 typ4 cnt4
                    typ5 cnt5 typ6 cnt6 typ7 cnt7);
  set have;
  by block;
  array typ (7) $ typ1-typ7;
  array cnt(7)  cnt1-cnt7;
  retain bcnt typ1-typ7 cnt1-cnt7;
  if first.block then do;
     call missing(of typ1-typ7);
	 call missing(of cnt1-cnt7);
	 bcnt=0;
  end;
  bcnt+1;
  typ(bcnt) = type;
  cnt(bcnt) = count;
  if last.block then output;
run;

title; footnote;
proc print data=want;
  var block bcnt typ1 cnt1 typ2 cnt2 typ3 cnt3 typ4 cnt4
                    typ5 cnt5 typ6 cnt6 typ7 cnt7;
run;

Re: How to copy multiple variables with transpose

PaigeMiller — Fri, 28 Dec 2018 17:06:45 GMT

Try the %TRANSPOSE macro, written by members of this community

http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

Re: How to copy multiple variables with transpose

SuryaKiran — Fri, 28 Dec 2018 17:09:50 GMT

Try this:

proc sort data=have;
by block type;
run;

proc transpose data=have out=count(drop=_name_) prefix=count;
by block;
var count;
run;
proc transpose data=have out=type(drop=_name_) prefix=type;
by block;
var type;
run;

data want;
merge type count;
by block;
run;

Re: How to copy multiple variables with transpose

Tom — Fri, 28 Dec 2018 17:39:00 GMT

Do you really want that structure? Your example makes the TYPE values look like names. Perhaps it would be better to treat them as such by using TYPE in an ID statement?

proc transpose data=have out=want2 ;
  by block;
  id type;
  var count;
run;

Obs    block    _NAME_     A     B    C     D     E

 1       2      count     10     .    .     .     .
 2       6      count      .    15    5     .     .
 3       8      count      .     .    .    25    32
 4       9      count      .     .    .     .    12

Re: How to copy multiple variables with transpose

SAShole — Fri, 28 Dec 2018 18:45:13 GMT

Thank you everyone for your solutions! I truly appreciate the helpful responses I always get from this community 🙂

Re: How to copy multiple variables with transpose

data_null__ — Fri, 28 Dec 2018 19:52:41 GMT

This one is good example for using PROC SUMMARY. Does not require sorting and will transpose both numeric and character at the same time without conversion. You do have to know the max number of obs per CLASS group width maximum 100.

data have;
	input block type $ count;
	datalines;
2 A 10
8 D 25
8 E 32
9 E 12
6 B 15
6 C 5
;
run;
proc print;
   run;
proc summary nway;
   class block;
   output out=wide(drop=_type_) idgroup(out[7](type count)=);
   run;
proc print;
   run;