Need Help with a Transpose

[asabou01]

Hi,

I am trying to transpose one record that I am having hard time.  An excel sheet with 2 TABS is attached.   TAB1 is the record I want to transpose.  TAB2 is what I need.    I also created corresponding datasets.  Any help is appreciated.

[ballardw · Accepted solution]

I suspect that your example data is incomplete but for what you show this gets the values you want:

Data want;
	length network $ 5; /* needs to be at least as long as the longest NET value*/

   set tab1;
	array n net1-net5;
	array nums hmob_be_num care_be_num mapo_be_num;
	array copy {3} $ 14 ;/* length is longest of the _num variables*/
	/* make copies of the _num variables*/
	do i=1 to dim(nums);
		copy[i]=nums[i];
	end;
	/* clear out NUMs */
	call missing (of nums(*));
   do i = 1 to dim(n);
		Network = n[i];
		select (n[i]);
			when ('HMOB') do; hmob_be_num = copy[1];
			                 output;
								  call missing(hmob_be_num);
			             end;
			when ('CARE') do; care_be_num = copy[1];
			                 output;
								  call missing(care_be_num);
			             end;
			when ('MAPO') do; mapo_be_num = copy[1];
			                 output;
								  call missing(mapo_be_num);
			             end;
			when ('252','P') output;
			otherwise put "WARNING: Unexpected value for Network " network=;
		end;
	end;
	drop i net1-net5 copy1-copy3;
run;

I generally don't worry about the specific column numbers variables end up in as I can control the output in any report needed that might want a specific layout.

[art297]

Another way to achieve what you want is with the untranspose macro. e.g.:

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=tab1, out=tab2 (drop=num rename=(net=NETWORK)), id=num,
  var=net, copy=provider_NPI--city)

data tab2(drop=hold);
  retain network;
  set tab2;
  hold=hmob_be_num;
  call missing (of hmob: care: mapo:);
  if network='HMOB' then hmob_be_num=hold;
  else if network='CARE' then care_be_num=hold;
  else if network='MAPO' then mapo_be_num=hold;
run;

Art, CEO, AnalystFinder.com