topic Re: SAS Combining columns of variables under one heading in SAS Programming

SAS Combining columns of variables under one heading

Leahk — Fri, 22 Jan 2016 02:38:46 GMT

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!

Re: SAS Combining columns of variables under one heading

Jagadishkatam — Fri, 22 Jan 2016 03:26:42 GMT

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;

Re: SAS Combining columns of variables under one heading

Reeza — Fri, 22 Jan 2016 03:42:47 GMT

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

Re: SAS Combining columns of variables under one heading

PGStats — Fri, 22 Jan 2016 04:40:48 GMT

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;

Re: SAS Combining columns of variables under one heading

Cynthia_sas — Fri, 22 Jan 2016 15:35:17 GMT

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