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 |
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
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.
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;
No, you need two Different Arrays. Otherwise you'll change variables while you work with them.
You're also missing steps 3 to 5
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: