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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.