topic Re: transpose the column and retain the missing columns in SAS Programming

transpose the column and retain the missing columns

tekish — Tue, 27 Dec 2016 19:26:13 GMT

I have a large dataset with too many columns and rows. i want to keep the all columns all the time.

Here is the samples sas code and desired out put.

data have;

input ID test;

cards;

1 01

1 02

1 03

1 04

1 05

1 06

2 02

2 04

2 05

2 06

3 08

3 09

3 10

4 01

4 08

4 10

run;

Desired output

ID	01	02	03	04	05	06	07	08	09	10
1	1	1	1	1	1	1
2		1		1	1	1
3								1	1	1
4	1							1		1

thanks,

Re: transpose the column and retain the missing columns

rogerjdeangelis — Tue, 27 Dec 2016 20:20:45 GMT

SAS Forum: Tranpoe and keeping missing values

inspired by
https://goo.gl/LzgjKJ
https://communities.sas.com/t5/Base-SAS-Programming/transpose-the-column-and-retain-the-missing-columns/m-p/321324


HAVE

data have;
input ID test;
cards;
1 01
1 02
1 03
1 04
1 05
1 06
2 02
2 04
2 05
2 06
3 08
3 09
3 10
4 01
4 08
4 10
run;

WANT

Up to 40 obs from want total obs=4

Obs    X1    X2    X3    X4    X5    X6    X7    X8    X9    X10

 1      1     1     1     1     1     1     .     .     .     .
 2      1     1     1     1     1     1     .     .     .     .
 3      1     1     1     1     1     1     .     1     1     1
 4      1     1     1     1     1     1     .     1     1     1


SOLUTION

data _null_;
  retain max 0;
  set have end=dne;
  if test > max then max=test;
  if dne then do;
     call symputx('max',put(max,3.));
     rc=dosubl('
     data want(keep=x1-x&max);
     do until (dne1);
       array xs x1-x&max;
       set have end=dne1;
       by id;
       xs[test]=(id>0);
       if last.id then output;
     end;
     stop;
  ');
  end;
run;quit;

Re: transpose the column and retain the missing columns

Patrick — Tue, 27 Dec 2016 21:02:03 GMT

You can get an output very close to your layout by using Proc Tabulate.

options missing=' ';
proc tabulate data=have noseps;
  class id test;
  format test z2.;
  table 
    id,
    test=' '*n=' '*f=2.
    /rts=10
  ;
run;

Re: transpose the column and retain the missing columns

Astounding — Wed, 28 Dec 2016 03:30:35 GMT

I'm surprised nobody proposed this already:

data addone;

set have;

one=1;

run;

proc transpose data=addone prefix=_ out=want (drop=_: ) ;

by id;

var one;

id test;

run;

Your data set needs to be sorted by ID to do this, but it looks like that is already the case. And you need to add a prefix for the variable names, since 01, 02, etc. are not valid variable names in SAS.

Re: transpose the column and retain the missing columns

Astounding — Thu, 29 Dec 2016 13:08:21 GMT

Curious. Your result is different than the poster's result. Wouldn't you want to get rid of the OUTPUT statement, and change the DO loop to look for (last.id) instead of (dne1)?

Re: transpose the column and retain the missing columns

Patrick — Thu, 29 Dec 2016 13:25:05 GMT

Sorry to say so @tekish and no offense meant @rogerjdeangelis but you really have chosen the most complicated approach as the solution to your problem.

If that's not just a once off then bear in mind that someone later on will have to understand and maintain this code and therefore keeping things simple is important.