Help using Base SAS procedures

change table layout

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

change table layout

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
memberFST_SRVC_DTTINofcmajcathipmajcathopmajcathofcmajctipmajctopmajct
1113/30/20125815211701 1
1113/30/2012582002413 1 1
1113/30/2012582002413 1 1
1118/16/20125820024131 1
1118/17/20125821767941
11110/17/2012581521170 1 1
11110/17/2012582002413 11

DATA NEED
memberFST_SRVC_DTTINofcmajcathipmajcathopmajcathofcmajctipmajctopmajctsub_table
1113/30/20125815211701 ofcmajcath
1113/30/2012581521170 1 opmajcath
1113/30/2012582002413 1 ipmajcath
1113/30/2012582002413 1opmajct
1113/30/2012582002413 1 opmajcath
1113/30/2012582002413 1 ipmajct
1118/16/20125820024131 ofcmajcath
1118/16/2012582002413 1 opmajcath
1118/17/20125821767941 ofcmajcath
11110/17/2012581521170 1 ipmajcath
11110/17/2012581521170 1 ofcmajct
11110/17/2012582002413 1 opmajcath
11110/17/2012582002413 1 ofcmajct

Accepted Solutions
Solution
‎10-01-2013 12:04 PM
Super Contributor
Posts: 339

Re: change table layout

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

View solution in original post


All Replies
Super User
Posts: 17,837

Re: change table layout

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.

Contributor
Posts: 69

Re: change table layout


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:ipmajcathSmiley SurprisedpmajcathSmiley Surprisedfcmajct:ipmajctSmiley SurprisedpmajctSmiley Surprisedfcmajpet:ipmajpetSmiley Surprisedpmajpet:

ofcmajperf:ipmajperfSmiley Surprisedpmajperf:;

array vars1(*) ofcmajcath:ipmajcathSmiley SurprisedpmajcathSmiley Surprisedfcmajct:ipmajctSmiley SurprisedpmajctSmiley Surprisedfcmajpet:ipmajpetSmiley Surprisedpmajpet:

ofcmajperf:ipmajperfSmiley Surprisedpmajperf:;

set cardiac.tinrestudies;

do i=1 to dim(vars1);

vars(i) = vars1(i);

end;

run;

Super User
Posts: 17,837

Re: change table layout

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

You're also missing steps 3 to 5

Solution
‎10-01-2013 12:04 PM
Super Contributor
Posts: 339

Re: change table layout

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 212 views
  • 0 likes
  • 3 in conversation