merged datasets

[tmcrouse]

I asked for help with a proc transpose and was told to create sep datasets and I did this but my proc transpose items are still not working the way they should with my merged datasets.

	strata	ofc_caths	ofc_pcis	ofc_secho	ofc_tte	ofc_perfs	op_caths	op_mpis	op_tte	ip_tte	mbr
HAVE	30	1	2	3	2	2		2	1	2	31
	89	4	5	2	11	3	6				29

WANT	strata	proc	office	op	ip
	30	cath	1/31	/31	/31
	30	pcis	2/31	/31	/31
	30	secho	3/31	/31	/31
	30	tte	2/31	1/31	2/31
	30	perfs	2/31	/31	/31
	30	mpis	./31	2/31	/31
	89	cath	4/29	6/29	/29
	89	pcis	5/29	/29	/29
	89	secho	2/29	/29	/29
	89	tte	11/29	/29	/29
	89	perfs	3/29	/29	/29
	89	mpis	/29	/29	/29

I tried proc tranpose but that does not work, then tried a data step with if statements creating a new row called office, then 1 op and the other ip. It does not look like the want data.

[Reeza · Accepted solution]

Here you go:

data want;

input strata    ofc_caths    ofc_pcis    ofc_secho    ofc_tte    ofc_perfs    op_caths    op_mpis    op_tte    ip_tte    mbr;

cards;

30    1    2    3    2    2    .    2    1    2    31

89    4    5    2    11    3    6    .    .    .    29

;

run;

data step1;

    set want;

    array vars(*) ofc: op: ip:;

    do i=1 to dim(vars);

        location=scan(vname(vars(i)), 1, "_");

        procedure=scan(vname(vars(i)), 2, "_");

        value=vars(i);

        value_display=put(value, 2.)||"/"||put(mbr, 3.);

        output;

    end;

    drop ofc: op: ip:;

run;

proc sort data=step1;

by strata  procedure location;

run;

proc transpose data=step1 out=want;

by strata procedure;

id location;

var value_display;

run;