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,
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;
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;
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)?
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;
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.
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.
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.
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.