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

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

5 REPLIES 5
rogerjdeangelis
Barite | Level 11
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;
Astounding
PROC Star

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

Patrick
Opal | Level 21

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;
Astounding
PROC Star

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.

Patrick
Opal | Level 21

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 2736 views
  • 3 likes
  • 4 in conversation