change table layout

[tmcrouse]

I have a table of 57k rows and want to change the way the table is laid out. Here is an example of the data in the main table and an example of what I want to do:

DATA HAVE
member	FST_SRVC_DT	TIN	ofcmajcath	ipmajcath	opmajcath	ofcmajct	ipmajct	opmajct
111	3/30/2012	581521170	1		1
111	3/30/2012	582002413		1				1
111	3/30/2012	582002413			1		1
111	8/16/2012	582002413	1		1
111	8/17/2012	582176794	1
111	10/17/2012	581521170		1		1
111	10/17/2012	582002413			1	1

DATA NEED
member	FST_SRVC_DT	TIN	ofcmajcath	ipmajcath	opmajcath	ofcmajct	ipmajct	opmajct	sub_table
111	3/30/2012	581521170	1						ofcmajcath
111	3/30/2012	581521170			1				opmajcath
111	3/30/2012	582002413		1					ipmajcath
111	3/30/2012	582002413						1	opmajct
111	3/30/2012	582002413			1				opmajcath
111	3/30/2012	582002413					1		ipmajct
111	8/16/2012	582002413	1						ofcmajcath
111	8/16/2012	582002413			1				opmajcath
111	8/17/2012	582176794	1						ofcmajcath
111	10/17/2012	581521170		1					ipmajcath
111	10/17/2012	581521170				1			ofcmajct
111	10/17/2012	582002413			1				opmajcath
111	10/17/2012	582002413				1			ofcmajct

[Reeza]

Create two arrays, 1 the old values and 1 the new values.

Loop through the old values.

Everytime it hits 1 then, set all to missing (call missing), then set the found value to 1.

Use Vname to get the  name of the variable for sub_table variable.

Use output to explicitly output the variable.

[tmcrouse]

The values are both the same, I just need to have 1 row for each.

Is this what you mean?

data cardiac.tinrestudiesb (keep=mbr_sys_id prov_tin fst_srvc_dt);

array vars(*) ofcmajcath:ipmajcath:opmajcath:ofcmajct:ipmajct:opmajct:ofcmajpet:ipmajpet:opmajpet:

ofcmajperf:ipmajperf:opmajperf:;

array vars1(*) ofcmajcath:ipmajcath:opmajcath:ofcmajct:ipmajct:opmajct:ofcmajpet:ipmajpet:opmajpet:

ofcmajperf:ipmajperf:opmajperf:;

set cardiac.tinrestudies;

do i=1 to dim(vars1);

vars(i) = vars1(i);

end;

run;

[Reeza]

No, you need two Different Arrays. Otherwise you'll change variables while you work with them.

You're also missing steps 3 to 5

[Vince28_Statcan · Accepted solution]

Double arrays conceptually...

data have;
input date date9. var1-var6;
format date date9.;
datalines;
01JAN2011 1 . 1 . 1 1
01FEB2011 1 . . . . 1
01MAR2011 1 . . . . .
;
run;

data want(rename=(dummy1=var1 dummy2=var2 dummy3=var3 dummy4=var4 dummy5=var5 dummy6=var6));
set have;
array dummy{6} dummy1-dummy6;
array var{6} var1-var6;
do i=1 to 6;
if var(i)=1 then do;
  dummy(i)=1;
  output;
  dummy(i)=.;
end;
end;
drop var1-var6 i;
run;

I didn't add the sub_table column I figured that was straight forward. It can be done within the if condition or in a separated data step.

Vince