Handling a lot of columns in Excel using SAS

1
votes

I have a database where i have close to 20kcolumns... which is the output of a transpose procedure in SAS.

Is it possible to split the output of an SAStranspose into Multiple csv files?. Like can i push 3000 columns(keeping the #rows thesame) into 5 CSVfiles?.. How can i do this with a SAS Macro?.

Basically, there is a variable which has 20k values per customer. I need to create a file(s) where that variable should be in columns.. my original data is something like below:

CustID Variable Value 1 4 0 1 3 23 1 1 22 1 2 18 2 4 0 2 3 23 2 1 22 2 2 18

So after transpose, it becomes: CustID Var1 Var2 Var 3 Var4 1 22 18 23 0
2 22 18 23 0

But problem is the "Variable" above has 20K values, so when transposed it becomes Var1 to Var20000.. which cannot fit in excel..

Any other way to handle this problem?.

I think Excel has a capacity of 16K columns, so i want to handle it in this way.. Please let me know how i can handle this situation

2
excelsasmultiple-columnstranspose
What on earth are you doing with the excel files that now have thousands of columns? Maybe if you give us some idea of what your end goal is, we might be able to propose a more workable solution. – orh
Who is the poor soul that received the excel workbooks, and what's the first thing they do with them? I'm willing to bet it's either nothing, or going some way to reversing what you're asking here :) – sasfrog

2 Answers

2
votes

I agree this is a bad idea, but here is another possible solution;

data big;
   cust_id = 1;
   retain var1-var20000 0;
run;

data temp/view=temp;
  set big(keep=cust_id var1-var10000);
run;

proc export data=temp
   outfile='c:\temp\file1.csv'
   dbms=csv
   replace;
run;

data temp/view=temp;
  set big(keep=cust_id var10001-var20000);
run;

proc export data=temp
   outfile='c:\temp\file2.csv'
   dbms=csv
   replace;
run;

To control the variables written, just change the view definition however you need. You originally asked about creating five CSVs, this creates two.

You have to use a view because PROC EXPORT does not respect KEEP or DROP data set options. I don't think using a macro to do something like this is a good idea unless you are very sure you know what you are doing AND you need to run it multiple times with different scenarios.

1
votes

I'll start off with 'wow, this is a bad idea'.

If you must do it, you can write your keep statement or even output code pretty easily.

proc contents data=sashelp.class out=names(keep=varnum name) noprint;
run;
proc sql noprint;
select name into :keeplist separated by ' ' from names where varnum le 2;
quit;
data forexport/view=forexport;
set sashelp.class;
keep &keeplist;
run;
proc export data=forexport outfile="c:\temp\whatever.csv" dbms=csv replace;
run;

You need the view unfortunately as the export with direct keeplist at least in 9.4 has an odd bug that removes the linefeeds.

You can then macro-ise the bit from the proc sql to the export and run that with different le/ge combinations. Make sure to include the matchkey on every single file.