topic Re: Proc transpose? in SAS Programming

Proc transpose?

ywon111 — Tue, 24 May 2022 00:26:17 GMT

Hi, hoping to get some help to get from this:

data have;
input ID Type $ Cost AppleValue OrangeValue;
infile datalines missover;
datalines;
1 Apple 810 1 .
1 Orange 2000 . 3
;

To this is what I need:

ID	Count_Apple	Count_Orange	AppleValueA	OrangeValueA
1	1	3	810	2000

Thanks

Re: Proc transpose?

Kurt_Bremser — Tue, 24 May 2022 04:39:21 GMT

This looks more like a report than a useful dataset structure. What do you intend to do with the result?

Re: Proc transpose?

Tom — Tue, 24 May 2022 14:35:14 GMT

Does your real data have the data only on the diagonal like that?

data have;
   input ID Type $ Cost Apple Orange Pear Zucchini ;
datalines;
1 Apple      810 1 . . .
1 Orange    2000 . 3 . .
1 Pear       300 . . 4 .
1 Zucchini   400 . . . 5
;

Or are some of the off diagonal cells non-missing? If so where are those values supposed to end up in the target dataset?

So perhaps you should first make the data taller and then transpose it.

data for_transpose;
  set have;
  by id ;
  row+first.id;
  length var $32 ;
  var = cats('Count',type);
  value = max(of apple -- zucchini);
  output;
  var = cats(type,'Value');
  value = cost;
  output;
run;

proc transpose data=for_transpose out=want;
  by id ;
  id var row;
  var value;
run;

Result