topic Transposing Data in SAS Programming

Transposing Data

JasonL — Tue, 01 Sep 2020 16:43:51 GMT

Hi there,

I have data in format similar to the following but with many more columns of "Codes" (Code_01 to Code_20) and corresponding "Indicators" (Indicator_01 to Indicator_20):

ID	Name	Age	Code_01	Code_02	Code_03	Indicator_01	Indicator_02	Indicator_03
001	John	42	AAA	BBB	CCC	No	No	Yes
002	Kathy	83	DDD	EEE	FFF	No	No	No
003	Joe	83	GGG	HHH	III	No	Yes	No

And need to convert it into something like the following:

ID	Name	Age	Code	Indicator
001	John	42	AAA	No
001	John	42	BBB	No
001	John	42	CCC	Yes
002	Kathy	83	DDD	No
002	Kathy	83	EEE	No
002	Kathy	83	FFF	No
003	Joe	83	GGG	No
003	Joe	83	HHH	Yes
003	Joe	83	III	No

Is there an efficient way to do that?

Thanks.
Jason

Re: Transposing Data

Tom — Tue, 01 Sep 2020 16:51:57 GMT

If it is just those two variables it is pretty easy to do with arrays.

data want ;
  set have;
  array _code code_01-code_20;
  array _ind indicator_01-indicator_20;
  do index=1 to dim(_code);
    Code = _code[index];
    Indicator = _ind[index];
    output;
  end;
  drop code_01-code_20 indicator_01-indicator_20;
run;

Re: Transposing Data

PaigeMiller — Tue, 01 Sep 2020 16:56:50 GMT

There's the %UNTRANSPOSE macro at https://support.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2419-2018.pdf

The example on page 2 seems exactly the same type of operation as you are asking about.

Re: Transposing Data

JasonL — Tue, 01 Sep 2020 17:00:40 GMT

Great thanks!