DATA Step, Macro, Functions and more

transpose the column and retain the missing columns

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

transpose the column and retain the missing columns

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,

 


Accepted Solutions
Solution
‎12-29-2016 07:35 AM
Valued Guide
Posts: 505

Re: transpose the column and retain the missing columns

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;

View solution in original post


All Replies
Solution
‎12-29-2016 07:35 AM
Valued Guide
Posts: 505

Re: transpose the column and retain the missing columns

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;
Super User
Posts: 5,513

Re: transpose the column and retain the missing columns

Posted in reply to rogerjdeangelis

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)?

Respected Advisor
Posts: 4,173

Re: transpose the column and retain the missing columns

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;
Super User
Posts: 5,513

Re: transpose the column and retain the missing columns

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.

Respected Advisor
Posts: 4,173

Re: transpose the column and retain the missing columns

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 240 views
  • 3 likes
  • 4 in conversation