BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmcrouse
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

4 REPLIES 4
Reeza
Super User

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
Calcite | Level 5


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
Super User

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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