SAS Combining columns of variables under one heading

[Leahk]

I have a data set that looks something like this:

idnum   var1   var2   var3   var4   var5   var6   var7   var8   var9   var10

1           a         b       12      e        2        3         4       5        6         7

2           v        f         15      g       5         6        7        8        9         10

3 ...

I want to combine var6-10 under one headin, but still have them seperate (if that makes sense). So it would end up something like this:

idnum   var1   var2   var3   var4   var5  newvar 

1           a        b        12      e        2      var6    3

                                                             var7    4

                                                             var8    5

                                                             var9    6

                                                             var10  7

2           v        f        15      g        5       var6    6

                                                             var7    7

                                                             var8    8

                                                             var9    9

                                                             var10  10

Is this even possible? I've attempted PROC TRANSPOSE but I can't figure it out.

Any help would be much appreciated!

[Jagadishkatam]

Please try arrays

data have;
input idnum   var1$   var2$   var3   var4 $  var5   var6   var7   var8   var9   var10;
cards;
1           a         b       12      e        2        3         4       5        6         7
2           v        f         15      g       5         6        7        8        9         10
;

data want;
set have;
by idnum;
array vs(5) var6   var7   var8   var9   var10;
do i = 1 to 5;
if i ne 1 then call missing(idnum, var1,var2,var3,var4,var5);
newvar=cat(strip(vname(vs(i))),' ',vs(i));
output;
end;
drop var6-var10 i;
run;

[Reeza]

Is this for a report or display purposes, or do you need a data set in this format?

[PGStats]

Something like this perhaps:

proc transpose data=have out=want name=namevar prefix=v;
by idnum   var1   var2   var3   var4   var5 notsorted;
var var6 -- var10;
run;

proc print data=want;
by idnum   var1   var2   var3   var4   var5 notsorted;
id idnum   var1   var2   var3   var4   var5;
run;

/* Or perhaps */
data want2;
set want;
newvar = cat(put(namevar,$6.),put(v1,3.));
drop namevar v1;
run;

proc print data=want2;
by idnum   var1   var2   var3   var4   var5 notsorted;
id idnum   var1   var2   var3   var4   var5;
run;

[Cynthia_sas]

Hi:

  If all you want is a report, here is another approach that shows 2 PROC REPORT steps (one with summarized total, one without) based on restructured data.

data start;

infile datalines dlm=',' dsd;

input idnum var1 $ var2 $ var3 var4 $ var5 var6 var7 var8 var9 var10;

return;

datalines;

1,a,b,12,e,2,3,4,5,6,7

2,v,f,15,g,5,6,7,8,9,10

3,q,w,11,h,1,1,1,1,1,1

;

run;

data final(keep=idnum var1 var2 var3 var4 var5 varnum varval);

set start;

** restructure data by making 1 obs for every var from 6-10;

** could put this in DO loop, but keeping it simple for this program;

varnum = 'var6';

varval = var6;

output;

varnum = 'var7';

varval = var7;

output;

varnum = 'var8';

varval = var8;

output;

varnum = 'var9';

varval = var9;

output;

varnum = 'var10';

varval = var10;

output;

run;

proc print data=final;

title 'See final structure';

run;

proc report data=final nowd;

title '1) Just Display the Other variables';

column idnum var1 var2 var3 var4 var5 ('Other Vars' varnum varval);

define idnum / order;

define var1 / order;

define var2 / order;

define var3 / order;

define var4 / order;

define var5 / order;

define varnum / display 'Var Name';

define varval / display 'Var Value';

run;

proc report data=final nowd;

title '2) Or, Add them Up';

column idnum var1 var2 var3 var4 var5 ('Other Vars' varnum varval);

define idnum / order;

define var1 / order;

define var2 / order;

define var3 / order;

define var4 / order;

define var5 / order;

define varnum / display 'Var Name';

define varval / sum 'Var Value';

break after idnum / summarize;

run;

 If all you want is a report, #1 is that report, but needs a separate row in the data for every var, var6-var10, where the values of var1-var5 stay the same on those rows (see the PROC PRINT output for the restructured data).

  Then report #2 summarizes var6-var10 for every IDNUM.

cynthia